Different Counts in a single query

user_1728459 0
Viewed 93

I have a table like this:

-----------------------
|  Name   | Date      |
-----------------------
| John    | July      |
| Carl    | August    |
| John    | July      |
| Robert  | August    |
| Carl    | September |
| John    | August    |
| Carl    | August    |
| John    | July      |
| Robert  | September |
| Carl    | August    |
-----------------------

I would like to count the names divided by month.

SELECT Name, 
COUNT(IF(`Date` = 'July',1,0)) AS July,
COUNT(IF(`Date` = 'August',1,0)) AS August,
COUNT(IF(`Date` = 'September',1,0)) AS September,
COUNT(*) AS All FROM table
GROUP BY Name

I tried this query, but the count values ​​are all the same

aggregate-functions null mysql sql
7 Answers

The reason why this happens is simple: COUNT() just counts the occurances of values. (= how many values are in the dataset?)

Looking at it this way, 0 is just another value like 1 is, too.

You can either do:

SELECT Name, 
COUNT(IF(`Date` = 'July', 1, NULL)) AS July,
COUNT(IF(`Date` = 'August', 1, NULL)) AS August,
COUNT(IF(`Date` = 'September', 1, NULL)) AS September,
COUNT(*) AS All FROM table
GROUP BY Name

This works because aggregate functions like COUNT() ignore NULL values.

Or, as others replied, you might just SUM() all the 1 values:

SELECT Name, 
SUM(`Date` = 'July') AS July,
SUM(`Date` = 'August') AS August,
SUM(`Date` = 'September') AS September,
COUNT(*) AS All FROM table
GROUP BY Name

This is written shorter, because the evaluation field = value already returns 1 if it matches or 0 otherwise. No need to wrap an IF() around to do exactly the same.

user_439590 
SELECT name,
  SUM(IF(`month` = 'July',1,0)) AS July,
  SUM(IF(`month` = 'August',1,0)) AS August,
  SUM(IF(`month` = 'September',1,0)) AS September,
  COUNT(*)  FROM test
GROUP BY name;

Reference: https://stackoverflow.com/a/13075582/1688441

user_16884410

The simplest way you can try this. condition aggregate function with bool (0 or 1) 

SELECT Name, 
    SUM(`Date` = 'July') AS July,
    SUM(`Date` = 'August') AS August,
    SUM(`Date` = 'September') AS September,
    COUNT(*) AS All 
FROM table
GROUP BY Name
user_51760710

COUNT counts non-null values, try SUM

SELECT Name, 
SUM(IF(`Date` = 'July',1,0)) AS July,
SUM(IF(`Date` = 'August',1,0)) AS August,
SUM(IF(`Date` = 'September',1,0)) AS September,
COUNT(*) AS All FROM table
GROUP BY Name
user_35529320

Try SELECT Name, Date, Count(*) as Count FROM table GROUP BY Name,Date

It should return something like this:

| Name  | Date      | Count |
-----------------------------
| Carl  | August    |   3   |
| Carl  | September |   1   |
| John  | July      |   3   |
| John  | August    |   1   |
| Robert| September |   1   |
| Rober | August    |   1   |

See this SQL Fiddle.

user_34293230

count counts values that are not null - including zeroes. With those ifs, you can use a sum to emulate a count - you'll effectively be counting the values that are 1s:

SELECT Name, 
SUM(IF(`Date` = 'July',1,0)) AS July,
SUM(IF(`Date` = 'August',1,0)) AS August,
SUM(IF(`Date` = 'September',1,0)) AS September,
SUM(*) AS All FROM table
GROUP BY Name
user_24227760 
select date, count(*) 
from table1
group by date

Should give you something like 

|DATE       |Count(*)|
----------------------
|August     |    5   |
|July       |    3   |
|September  |    2   |

http://sqlfiddle.com/#!9/c0a483/2

user_18460400
Related