Sample Header Ad - 728x90

SQL: Running total with group by

-2 votes
1 answer
311 views
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 4 Feb/1/2021 Feb/1/2021 5 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
Asked by Rishtin (1 rep)
Jul 26, 2021, 04:42 PM
Last activity: Jul 27, 2021, 10:02 PM