I'm using a MySQL Server instance with certain constraints (no CTE, not able to create temporary tables or even new tables), so the only way allowed to solve it is using derived tables.
I have the following table t, containing each occurrence of each selling on each month (1-12):
+------+-----------+-----------------+
| id | product | sold_on_month |
+------+-----------+-----------------+
| 1 | Product 1 | 1 |
| 2 | Product 1 | 2 |
| 3 | Product 1 | 2 |
| 4 | Product 1 | 3 |
| 5 | Product 1 | 3 |
| 6 | Product 1 | 4 |
| 7 | Product 1 | 4 |
| 8 | Product 1 | 4 |
| 9 | Product 1 | 4 |
| 10 | Product 1 | 4 |
| 11 | Product 2 | 1 |
| 12 | Product 2 | 1 |
| 13 | Product 2 | 2 |
| 14 | Product 2 | 2 |
| 15 | Product 2 | 3 |
| 16 | Product 2 | 3 |
+------+-----------+-----------------+
I would like to create a query that answers me how many products were sold, by product and by month, like this:
+-----------+---------+------------+
| product | month | how_many |
+-----------+---------+------------+
| Product 1 | 1 | 1 |
| Product 1 | 2 | 2 |
| Product 1 | 3 | 2 |
| Product 1 | 4 | 5 |
| Product 1 | 5 | 0 |
| Product 1 | 6 | 0 |
| Product 1 | 7 | 0 |
| Product 1 | 8 | 0 |
| Product 1 | 9 | 0 |
| Product 1 | 10 | 0 |
| Product 1 | 11 | 0 |
| Product 1 | 12 | 0 |
| Product 2 | 1 | 2 |
| Product 2 | 2 | 2 |
| Product 2 | 3 | 2 |
| Product 2 | 4 | 0 |
| Product 2 | 5 | 0 |
| Product 2 | 6 | 0 |
| Product 2 | 7 | 0 |
| Product 2 | 8 | 0 |
| Product 2 | 9 | 0 |
| Product 2 | 10 | 0 |
| Product 2 | 11 | 0 |
| Product 2 | 12 | 0 |
+-----------+---------+------------+
What I achieved by now: if I had a similar table without column product and if I wanted to group by month, the query would be this:
SELECT
a.mn, COUNT(t.sold_on_month)
FROM
(SELECT 1 AS mn UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) AS a
LEFT JOIN t
ON t.sold_on_month = a.mn
GROUP BY a.mn;
So, what I really need is a query that uses the same derived table for counting months and a group by product and sold_on_month, showing 0 on how_many column when no products were sold on that month. Remembering I can't use CTE or temporary tables.
Any help is appreciated! :)
Generate a distinct list of products with a query. (If you have a products table, you could reference that.)
Given only the table in the question, we could get a distinct list like this
SELECT q.product
FROM t q
GROUP
BY q.product
We can wrap that in parens as an inline view (a derived table, in the MySQL parlance) and then do a cross join to the generated list of months,
SELECT p.product
, a.mn
FROM ( -- distinct list of products
SELECT q.product
FROM t q
GROUP
BY q.product
) p
CROSS
JOIN ( -- list of months
SELECT 1 AS mn UNION ALL ...
) a
ORDER
BY p.product
, a.mn
And then we can add in the outer join to t, and do a GROUP BY and aggregate in the SELECT list ...
SELECT p.product
, a.mn
-- , IFNULL(SUM(t.qty),0) AS tot_qty
, COUNT(t.sold_on_month) AS cnt_rows
FROM ( -- distinct list of products
SELECT q.product
FROM t q
GROUP
BY q.product
) p
CROSS
JOIN ( -- list of months
SELECT 1 AS mn UNION ALL ...
) a
LEFT
JOIN t t
ON t.product = p.product
AND t.sold_on_month = a.mn
GROUP
BY p.product
, a.mn
ORDER
BY p.product
, a.mn
If you have another source for the inline view p, other than the table in the question (e.g. a products table with a distinct list of products), we could reference that instead.
The trick is the CROSS JOIN between "months" and "products".
Then an outer join to get matching rows from t.