MYSQL get multiple fields count using Group by multiple fields by date

Question

I want count by using group two columns. I have data like this:

id     sectionid    date

1        1          2015-09-16
2        1          2015-09-16
3        2          2015-09-16
4        2          2015-09-16
5        3          2015-09-16
6        1          2015-09-17
7        2          2015-09-18
8        2          2015-09-18

Result will be:

 Date          section1count   section2count  section3count

 2015-09-16      2               2              1
 2015-09-17      1               0              0
 2015-09-18      0               2              0

Thanks in advance.

possible duplicate of http://stackoverflow.com/questions/7674786/mysql-pivot-table — KrazzyNefarious, Sep 16 '15 at 05:51

Tim Biegeleisen · Accepted Answer · 2015-09-16T05:59:37.290

2

You can use a combination of the SUM() function along with GROUP BY to get the result set you want:

SELECT date AS Date,
       SUM(CASE WHEN sectionid=1 THEN 1 ELSE 0 END) AS section1count,
       SUM(CASE WHEN sectionid=2 THEN 1 ELSE 0 END) AS section2count,
       SUM(CASE WHEN sectionid=3 THEN 1 ELSE 0 END) AS section3count
FROM table
GROUP BY date

SQLFiddle

edited Sep 16 '15 at 05:59

answered Sep 16 '15 at 05:52

Tim Biegeleisen

502,043
27
286
360

@TimBiegeleisen can you explain how this query gets executed. Is the CASE WHEN in the sum filtering the rows to sum against? – KAD Sep 16 '15 at 06:25
Each `CASE WHEN` outputs a value into one of three count columns, depending on the value of the `sectionid` for that record. The `GROUP BY` aggregates by the `date` and takes the `SUM` of the three counts. – Tim Biegeleisen Sep 16 '15 at 06:27
So this is doable since the `SELECT` executes before `GROUP BY` so the `SUM` in the `section1count` will be `SUM(1)` for rows with `sectionid=1` and `SUM(0)` for rows with `sectionid!=1`. Then when the `GROUP BY` aggregates it shall sum the rows with `SUM(1)`... Is this how it goes?. Am trying to analyze the execution process please correct me if am wrong.. – KAD Sep 16 '15 at 06:33
The temporary table will contain three columns, one for each `sectionid` count. During the `GROUP BY` operation, MySQL will `SUM` these columns, which as you pointed out will contain either zeroes or ones. This gives you your result. – Tim Biegeleisen Sep 16 '15 at 06:37
1

Thank you for your clarification :) – KAD Sep 16 '15 at 06:38

score 0 · Answer 2 · answered Sep 16 '15 at 06:00

her is my result. You can compare it to count each section

SELECT 
  SUM(IF( m.sectionid = 1 , 1,0)) section1count,
  SUM(IF( m.sectionid = 2 , 1,0)) section2count,
  SUM(IF( m.sectionid = 3 , 1,0)) section3count,
  m.date
   FROM myt m
   GROUP BY m.`date`;

MYSQL get multiple fields count using Group by multiple fields by date

2 Answers2