0

I'm sure this question has been asked, but I can't quite figure out what to Google.

I have a big database of beer purchases listed by order date and type, like so:

order_date | beer_type
-----------
2014-01-04 | amber
2014-01-05 | lager
2014-04-01 | amber

There are 1.4 million records, and I want to get them into a table with the month of the purchase as rows and the quantity of each type purchases as columns:

month | amber | lager | wheat
-----------------------------
1     | 2092  | 3030  | 990
2     | 1890  | 2025  | 1028

I have no trouble with the actual grouping:

SELECT beer_type, MONTH(order_date) AS month, COUNT(*) AS count 
FROM purchases
GROUP BY month,beer_type 
ORDER BY month,beer_type ASC

But of course, that still gives me a long two-column table which I then have to nest myself. But I don't want to write a query in which I have to manually enter all the beer_types. There must be an easy way to nest results like this?

Chris Wilson
  • 6,599
  • 8
  • 35
  • 71
  • Look into group_concat https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat – dbinns66 Apr 28 '15 at 15:21
  • You need to define a 'pivot' table. Possible duplicate 'http://stackoverflow.com/questions/7674786/mysql-pivot-table'. – K139 Apr 28 '15 at 15:41
  • Looks like "Pivot" was the word I needed for search. Will read other questions. Thx! – Chris Wilson Apr 28 '15 at 16:10

0 Answers0