MySQL orders by category by month

Question

I have a table of orders with these columns: order_item order_category order_date so the data looks like so:

order_item  order_category   order_date 
1235        hardware         2014-01-07
1349        bedding          2014-01-07
1636        hardware         2014-01-08

etc.

There are about 20 product categories. This query gets me how many items were ordered by category each day:

 SELECT order_date, count(order_item) as num, order_category  
 FROM orders
 GROUP BY order_date, order_category

I would like to get a result that shows month and the count for each category, so it would look like this:

 month    bedding  hardware etc
 2013-12  5        8
 2014-01  9        0

Can I do this with mysql?

This question appears to be off-topic because it lacks basic research — Strawberry, May 10 '14 at 17:00
The issue isn't aggregating by month, it is pivoting the categories. With that hint, you should be able to google to a working solution within 30 seconds. — Gordon Linoff, May 10 '14 at 17:08

score 0 · Answer 1 · edited May 23 '17 at 12:31

0

You also have to group by year or you get the results for 'every january' of every year.

SELECT order_date, count(order_item) as num, order_category  
FROM orders
GROUP BY order_category, year(datefield), month(date field) desc;

a simple google for 'mysql group by year and month' gives this fine post...

Group by month and year in MySQL

edited May 23 '17 at 12:31

Community

1
1

answered May 10 '14 at 17:03

Jonathan

1,542
3
16
24

The OP is really asking for the results to be pivoted. – Gordon Linoff May 10 '14 at 17:07

score 0 · Accepted Answer · answered May 10 '14 at 17:09

Try this :

   SELECT '2014-01' month , SUM(CASE WHEN order_category = 'hardware' 
                        THEN 1 ELSE 0 END )as hardware, 
                            SUM(CASE WHEN order_category = 'bedding' 
                        THEN 1 ELSE 0 END )as bedding
  FROM Table1
  WHERE MONTH(order_date) = 01 AND  year(order_date) = 2014

  UNION all 

  SELECT '2013-12' month , SUM(CASE WHEN order_category = 'hardware' 
                        THEN 1 ELSE 0 END )as hardware, 
                           SUM(CASE WHEN order_category = 'bedding' 
                        THEN 1 ELSE 0 END )as bedding
  FROM Table1
  WHERE MONTH(order_date) = 12 AND  year(order_date) = 2013

DEMO

Thanks for all the comments. This is terrific and I did use varchar for the date as it happens - but I guess I cannot avoid naming every category in the query? — gorilla5, May 10 '14 at 17:17

MySQL orders by category by month

2 Answers2