Oracle SQL - trying to calculate running total with Group By without having an existing numerical column to sum

Question

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.

It would be good to show based on your example, how the end data would look like — George Joseph, Jul 28 '20 at 02:40

score 0 · Accepted Answer · answered Jul 27 '20 at 23:50

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

Oracle SQL - trying to calculate running total with Group By without having an existing numerical column to sum

1 Answers1