mysql查询增加总计列,MySQL查询返回多个总计列

I have the following data in my SQL table.

uid user category count date

--------------------------------------------------

1 henry RED 4 2013-05-01

2 john BLUE 3 2013-05-01

3 alice RED 5 2013-05-01

4 eric GREEN 2 2013-05-02

5 eric BLUE 2 2013-05-02

6 john RED 3 2013-05-02

7 henry GREEN 2 2013-05-02

8 eric RED 3 2013-05-03

9 john BLUE 5 2013-05-03

I would like a query that gives me back the following data

category 2013-05-01 2013-05-02 2013-05-03

---------------------------------------------------

RED 9 3 3

BLUE 3 2 5

GREEN 0 4 0

I can get one column of data with the following query, but I do not know how to get multiple columns grouped as shown above. I'd like to have one query that my PHP renders instead of looping through each date.

SELECT category, SUM(count)

FROM dbtable

WHERE date='2013-05-01'

GROUP BY category;

Is this possible? Ideas?

解决方案

Something like this should work:

SELECT

category,

SUM(CASE WHEN date = '2013-05-01' THEN count END) AS `2013-05-01`,

SUM(CASE WHEN date = '2013-05-02' THEN count END) AS `2013-05-02`,

SUM(CASE WHEN date = '2013-05-03' THEN count END) AS `2013-05-03`

FROM dbtable

GROUP BY category