I'm wondering if anyone can help me. I've got the following table structure, and I'm trying to get a running total of the count of products, grouped by date and product, i.e. for each distinct date in Date_Ordered, I want each distinct Product listed, and the sum of how many times it's appeared up to, and including, that date.
+-----------+------------+-------------+-----+ | Reference | Product | Date_Orderd | ... | +===========+============+=============+=====+ | x-123123 | Product 1 | 02/02/2020 | ... | +-----------+------------+-------------+-----+ | x-123124 | Product 2 | 02/02/2020 | ... | +-----------+------------+-------------+-----+ | x-123125 | Product 3 | 02/02/2020 | ... | +-----------+------------+-------------+-----+ | ... | ... | ... | ... | +-----------+------------+-------------+-----+ | x-123241 | Product 2 | 24/03/2020 | ... | +-----------+------------+-------------+-----+ | x-123242 | Product 1 | 25/03/2020 | ... | +-----------+------------+-------------+-----+ | ... | ... | ... | ... | +-----------+------------+-------------+-----+ | x-123620 | Product 10 | 02/05/2020 | ... | +-----------+------------+-------------+-----+ | x-123621 | Product 7 | 02/05/2020 | ... | +-----------+------------+-------------+-----+
The problem I'm having is that all the examples I've found for this (e.g. https://codingsight.com/calculating-running-total-with-over-clause-and-partition-by-clause-in-sql-server/ , https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1793764100346222947 , https://medium.com/better-programming/4-ways-to-calculate-a-running-total-with-sql-986d0019185c) seem to assume that there's a column in the table with a numerical value to be summed.
Does anyone know if there's a way to get the data I need?
Cheers in advance.
If a given product is never ordered twice on the same data, you can just use a window count:
select
t.*,
count(*) over(partition by reference order by date_ordered) running_count
from mytable t
If there are duplicates, then you need aggregation:
select
reference,
date_ordered,
sum(count(*)) over(partition by reference order by date_ordered) running_count
from mytable
group by reference, date_ordered
Finally: if you want to generate all combinations of dates and product, with the associated runnig count, then you would do:
select
r.reference,
d.date_ordered,
sum(count(t.reference)) over(partition by r.reference order by d.date_ordered) running_count
from (select distinct date_ordered from mytable) d
cross join (select distinct reference from mytable) r
left join mytable t
on t.date_ordered = d.date_ordered and t.reference = r.reference
group by d.date_ordered, r.reference