CODEWITHSUNDEEP
mysqlsql
Araejay
Araejay

Reputation: 222

MySQL Count + Grouping for a Field value

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.

Upvotes: 0

Views: 324

Answers (2)

juergen d
juergen d

Reputation: 204766

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`

Upvotes: 3

Eugen Rieck
Eugen Rieck

Reputation: 65274

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.

Upvotes: 1

Related Questions