i have this table –> https://pasteboard.co/IrVXlRe.png
and there is another table –> https://pasteboard.co/IrVXNPh.png
what i need is to show ALL categories regardless of the quantity. if there have been no sales in a certain category, I also want to show a record where the quantity is zero
The result I want to get is like the following –> https://pasteboard.co/IrVYgSK.png
I have a table with many records… the sales_per_month table is the result of a query to get the subtotals.
the categories table has an ID column, I just haven't put it here for simplicity's sake
with this query
select b.year, b.month, a.category, coalesce(c.quantity, 0) as quantity
from categories a
cross join sales_per_month b
left join sales_per_month c on c.year = b.year and c.month = b.month and c.category = a.category
group by b.year, b.month, a.category
order by b.year desc, b.month desc, a.category
I get all the records from the categories table, but only once, but what I need is all categories to be repeated for each year/month group.
i need all categories to always appear in each group, as my ultimate goal is to add six more columns to this table from other tables that are exactly the same but with different totals. in short, i want to normalize the number of rows. i want the seven tables to have exactly the same number of rows (the same number of categories) so that i can add the other columns of totals.
expected result –> https://ibb.co/f1dbTs9