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
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
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