Different Counts in a single query

5
votes

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

7
mysqlsqlnullaggregate-functions
And what is the result? - Menelaos
In MySQL COUNT(IF(Date = 'July',1,0)) can also be COUNT(Date = 'July') it does not matter. mind the backticks tho around Date - Raymond Nijland
Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example - Juan Carlos Oropeza
Possible duplicate of MySql: is it possible to 'SUM IF' or to 'COUNT IF'? - mleko

7 Answers

3
votes

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.

2
votes
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

1
votes

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
0
votes

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
0
votes

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
0
votes
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

0
votes

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.