开发者

How can I GROUP BY more than one row/column?

I'm looking for a way to group more than one column in mysql. The table is pretty much:

Customer, Product, Date

Joe, Apple, 2011-01-01
Henry, Banana, 2011-05-26
Sally, Peach, 2011-06-02
Jane, Strawberry, 2010-06-25

What I wanna do is count the NUMBER of Produkts each customer bought and group that by the month of the year.

So it would look like.

COUNT(Product) | January | Febru开发者_StackOverflowary | March ....... | Total

If I just use GROUP BY product, MONTH(date) I get

Banana, January, 2
Banana, February, 3
Banana, March, 1

and so on.

Is there a way to do this, so that I get as many rows as I have distinct products and then a column for each month? I'm really trying NOT to do this lateron in PHP because it get's incredibly slow. Thanks a lot, already!


SELECT Product,
 SUM(CASE WHEN MONTH(date) =  1 THEN 1 ELSE 0 END) Jan,
 SUM(CASE WHEN MONTH(date) =  2 THEN 1 ELSE 0 END) Feb,
 SUM(CASE WHEN MONTH(date) =  3 THEN 1 ELSE 0 END) Mar,
 SUM(CASE WHEN MONTH(date) =  4 THEN 1 ELSE 0 END) Apr,
 SUM(CASE WHEN MONTH(date) =  5 THEN 1 ELSE 0 END) May,
 SUM(CASE WHEN MONTH(date) =  6 THEN 1 ELSE 0 END) Jun,
 SUM(CASE WHEN MONTH(date) =  7 THEN 1 ELSE 0 END) Jul,
 SUM(CASE WHEN MONTH(date) =  8 THEN 1 ELSE 0 END) Aug,
 SUM(CASE WHEN MONTH(date) =  9 THEN 1 ELSE 0 END) Sep,
 SUM(CASE WHEN MONTH(date) = 10 THEN 1 ELSE 0 END) Oct,
 SUM(CASE WHEN MONTH(date) = 11 THEN 1 ELSE 0 END) Nov,
 SUM(CASE WHEN MONTH(date) = 12 THEN 1 ELSE 0 END) Dec,
 COUNT(date) Total
FROM 
 MyTable
GROUP BY 
 product
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜