MySQL query to return multiple summed columns

2019-07-31 13:16发布

问题:

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