Query using group by with 2 columns

Hello,

Can anyone possibly help me with this query?

select product_name, 
count(*) as amt_ordered from phpcart_items_ordered 
WHERE date_ordered BETWEEN CAST('2014-07-01' AS DATE) 
AND CAST('2014-07-31' AS DATE) 
group by product_name 
order by amt_ordered desc

I need to modify this query above to give me a list of the qty. of each product sold in each month from July 2014 to Feb 2015

Example:

product_name July Aug Sept
white shirt 10 6 7
black shoes 2 10 5

Any help would be greatly appreciated :smile:

SELECT product_name , COUNT(CASE WHEN MONTH(date_ordered) = 07 THEN 1 END) AS jul , COUNT(CASE WHEN MONTH(date_ordered) = 08 THEN 1 END) AS aug , COUNT(CASE WHEN MONTH(date_ordered) = 09 THEN 1 END) AS sep , COUNT(CASE WHEN MONTH(date_ordered) = 10 THEN 1 END) AS oct , COUNT(CASE WHEN MONTH(date_ordered) = 11 THEN 1 END) AS nov , COUNT(CASE WHEN MONTH(date_ordered) = 12 THEN 1 END) AS dec , COUNT(CASE WHEN MONTH(date_ordered) = 01 THEN 1 END) AS jan , COUNT(CASE WHEN MONTH(date_ordered) = 02 THEN 1 END) AS feb FROM phpcart_items_ordered WHERE date_ordered >= CAST('2014-07-01' AS DATE) AND date_ordered < CAST('2015-03-01' AS DATE) GROUP BY product_name

Thank you so much for the reply!!
I was told on another forum it couldn’t be done :smile:

I got an error with the code?
Any ideas?

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘dec
, COUNT(CASE WHEN MONTH(date_ordered) = 01 THEN 1 END) AS jan
, CO’ at line 7

looks like “dec” is a reserved word (decimal)

put it into backticks –

...) AS `dec`
1 Like

WOW! You are a star!!
The code worked perfectly, thank you soon much :smile: :smile:

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.