I use MySQL and I want to make a table that displays: by product category, the turnover for each month for the last 12 months regardless of when the request was made.
Some months, some products are not sold.
I would like that for EACH MONTH, ALL the categories are displayed (7) and that the empty values in "total_sales" are indicated in NULL. But the months must be displayed even if the total_sales is NULL
Do you have any ideas? I've been looking for several hours but I can't find anything
Here is my code :
SELECT
p.productLine,
DATE_FORMAT( o.orderDate, "%M" ) AS month,
SUM( od.quantityOrdered * od.priceEach ) AS total_sales
FROM
products AS p
LEFT OUTER JOIN orderDetails AS od ON p.productCode = od.productCode
LEFT OUTER JOIN orders AS o ON od.orderNumber = o.orderNUmber
WHERE
o.orderDate BETWEEN DATE_ADD( NOW(), INTERVAL - 12 MONTH ) AND NOW()
GROUP BY
MONTH(o.orderDate),
p.productLine
ORDER BY
o.orderDate DESC
Here is the actual result:
