Ytukyg

Using information of this site I have been able to do the join but im having the issues doing the group by

is there a way to get the below statements to run as 1

Query1

SELECT count(location),date 

from `filter` 

where location != "red" 

group by date 

Query2

SELECT count(location),date 

from `filter` 

where location = "red" 

group by date 

I did try the below but it outputs the wrong data

Query3

SELECT 

date,

(select count(location) from `filter` where location != "red") AS indoor, 

(select count(location) from `filter` where location = "red") AS outdoor

from `filter` group by date;

SQL Fiddle for each query

http://sqlfiddle.com/#!9/17ebea/4 (query1)

http://sqlfiddle.com/#!9/17ebea/6 (query2)

http://sqlfiddle.com/#!9/90c945/1 (query3)

SELECT 

    date,

    COUNT(CASE WHEN location <> 'red' THEN location ELSE NULL END) AS indoor,

    COUNT(CASE WHEN location = 'red' THEN location ELSE NULL END) AS outdoor

FROM filter 

GROUP BY date;

https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html#operator_case

You can do conditional aggregation using CASE.. WHEN expressions.

SELECT 

  date,

  COUNT(CASE WHEN location = 'red' THEN location END) AS outdoor,

  COUNT(CASE WHEN location <> 'red' THEN location END) AS indoor

FROM `filter` 

GROUP BY date;

View on DB Fiddle

Result

| date       | outdoor | indoor |

| ---------- | ------- | ------ |

| 2018-11-14 | 1       | 4      |

| 2018-11-15 | 1       | 0      |

| 2018-11-16 | 0       | 3      |

| 2018-11-17 | 1       | 1      |

| 2018-11-18 | 0       | 1      |

| 2018-11-19 | 0       | 2      |

| 2018-11-20 | 0       | 1      |

You can also use the following other variants, like using COUNT(1) instead, or using SUM(..) function.

Alternative #1

SELECT 

  date,

  COUNT(CASE WHEN location = 'red' THEN 1 END) AS outdoor,

  COUNT(CASE WHEN location <> 'red' THEN 1 END) AS indoor

FROM `filter` 

GROUP BY date;

Alternative #2

SELECT 

  date,

  SUM(CASE WHEN location = 'red' THEN 1 ELSE 0 END) AS outdoor,

  SUM(CASE WHEN location <> 'red' THEN 1 ELSE 0 END) AS indoor

FROM `filter` 

GROUP BY date;

In MySQL, I would use the shortcut that allows you to sum() boolean variables:

select date, sum(location = 'red') as red,

       sum(location <> 'red') as not_red

from filter 

group by date ;

Notes:

• Use single quotes for string and date constants -- not double quotes. Single quotes are the standard delimiter.


• 
  <> is the SQL inequality operator, although != is also supported by most databases.


• This does not count NULL values.

To handle NULL values, you might want:

       sum(not location <=> 'red') as not_red