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 stackoverflow.com/questions/7674786/mysql-pivot-table — KrazzyNefarious
– KrazzyNefarious, Commented Sep 16, 2015 at 5:51

Tim Biegeleisen · Accepted Answer · 2015-09-16 05:59:37Z

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, 2015 at 5:59

answered Sep 16, 2015 at 5:52

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

KAD Over a year ago

@TimBiegeleisen can you explain how this query gets executed. Is the CASE WHEN in the sum filtering the rows to sum against?

Tim Biegeleisen Over a year ago

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.

KAD Over a year ago

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..

Tim Biegeleisen Over a year ago

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.

Bernd Buffen · Accepted Answer · 2015-09-16 06:00:16Z

0

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`;

answered Sep 16, 2015 at 6:00

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related