SQL: Running total with group by

Question

I have a dataset that has two date columns for each transaction. An invoice date and a charge date. I am trying to do a cumulative or running total of the table in sql.

Data table looks like this
InvoiceDate ChargeDate TransactionID
jan/1/2021 jan/1/2021 11111
jan/1/2021 jan/1/2021 55555
jan/1/2021 Feb/1/2021 11111
jan/1/2021 Feb/1/2021 22222
jan/1/2021 Feb/1/2021 33333
jan/1/2021 Feb/1/2021 44444
Feb/1/2021 jan/1/2021 11111
Feb/1/2021 jan/1/2021 22222
Feb/1/2021 jan/1/2021 33333
Feb/1/2021 jan/1/2021 44444
Feb/1/2021 Feb/1/2021 55555

I want the output in this format
InvoiceDate ChargeDate cumulativeCount
jan/1/2021 jan/1/2021 2
jan/1/2021 Feb/1/2021 5
Feb/1/2021 jan/1/2021 3
Feb/1/2021 Feb/1/2021 4

I tried the following code but it doesn't give me a running total. it gives me total of each of the two columns

    select
    chargeDate,
    InvoiceDate,
    Count(Distinct TransactionID) as CountofIDs
    from mytable1
where InvoiceDate<=InvoiceDate
    group by ChargeDate, InvoiceDate

Tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2021-07-25 15:50:18 +00:00
Commented Jul 25, 2021 at 15:50 — Gordon Linoff
– Gordon Linoff, Commented Jul 25, 2021 at 15:50

Gordon Linoff · Accepted Answer · 2021-07-25 15:51:36Z

0

Hmmm . . . If I following correctly, you want aggregation with a window function.

select invoicedate, chargedate,
       count(*) as cnt_on_dates,
       sum(count(*)) over (partition by invoicedate order by chargedate) as cumulative_count
from t
group by invoicedate, chargedate;

answered Jul 25, 2021 at 15:51

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rishtin Over a year ago

sorry i forgot to mention that over function is not supported. I believe its ver 2005

Collectives™ on Stack Overflow

SQL: Running total with group by

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related