Q:

SUM of rows with column category in mysql

I have table in mysql like following
Table Data :
   id        items     color        date
    1          22       blue      2014-05-25
    2         344       yellow    2014-05-26
    3         255       blue      2014-05-26
    4         234       blue      2014-05-26
    5         200       yellow    2014-05-27
    6         100       blue      2014-05-27
    7         100       yellow    2014-05-25
    8         233       blue      2014-05-26

I need sum of items based on color with Grouped by Date ...
like 
I NEED OUTPUT LIKE THIS:
Date        Items    blue  yellow
2014-05-25    122      22   100
2014-05-26   1066     722   344
2014-05-27    300     100   200

I need Mysql Query to get output given in above..

A:

You would do normal group by Date
and according to http://dev.mysql.com/doc/refman/5.0/en/case.html
you should 
select Date, sum(items) as Items, 
sum(case when color = 'blue' then color else 0 end case) as blue,
sum(case when color = 'yellow' then color else 0 end case) as yellow
from ...
group by Date

Sorry if syntax's missmatched. I don't do MySQL.

