MySQL Count + Grouping for a Field value

Question

I have a table:

Date       | Code

2012-04-01 | 1
2012-04-01 | 0
2012-04-01 | 0
2012-04-01 | 2
2012-04-02 | 4
2012-04-03 | 1
2012-04-03 | 0
2012-04-03 | 0
2012-04-04 | 1
2012-04-04 | 3
2012-04-04 | 4
2012-04-04 | 4
2012-04-04 | 0

Code is predefined from 0-5.

What I would like is a query that Counts by the Code & Date, where each Record is for 1 Date. Each predefined code should be counted in a separate field.

So, the Result should be like:

Date       | Code0 | Code1 | Code2 | Code3 | Code4 | Code5

2012-04-01 |     2 |     1 |     1 |     0 |     0 |     0 
2012-04-02 |     0 |     0 |     0 |     0 |     1 |     0 
2012-04-03 |     2 |     1 |     0 |     0 |     0 |     0 
2012-04-04 |     1 |     1 |     0 |     3 |     4 |     0

I would like to do this in the most efficient way possible, the data size would be around 400,000, so some union based queries may hurt the database.

Thanks in advance.

juergen d · Accepted Answer · 2012-07-18 07:53:24Z

3

try

select `date`, 
       sum(code=0) as code0, 
       sum(code=1) as code1,
       sum(code=2) as code2,
       sum(code=3) as code3, 
       sum(code=4) as code4, 
       sum(code=5) as code5           
from your_table
group by `date`

answered Jul 18, 2012 at 7:53

juergen d

205k40 gold badges305 silver badges377 bronze badges

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

Comments

Eugen Rieck · Accepted Answer · 2012-07-18 07:53:03Z

1

SELECT
  `Date`,
  SUM(IF(`Code`=0,1,0)) AS `Code0`,
  SUM(IF(`Code`=1,1,0)) AS `Code1`,
  SUM(IF(`Code`=2,1,0)) AS `Code2`,
  SUM(IF(`Code`=3,1,0)) AS `Code3`,
  SUM(IF(`Code`=4,1,0)) AS `Code4`
FROM
  tablename
GROUP BY `Date`

This will traverse your table only once, which is as good as it gets.

answered Jul 18, 2012 at 7:53

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

2 Comments

Araejay Over a year ago

However, I would like to add that I was looking for the occurrence of the data rather the sum of it, so COUNT instead of SUM in my case.

Eugen Rieck Over a year ago

@Araejay: The SUM() of either 1 or 0 is the same thing as the count of the matching rows. COUNT() doesn't allow filters, so I chose SUM(IF()) as a conditional count.

Collectives™ on Stack Overflow

MySQL Count + Grouping for a Field value

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related