CODEWITHSUNDEEP
sqlsql-servert-sqlanalytics
BrackleyMan
BrackleyMan

Reputation: 19

Running Total by Transaction Date & ProductID

Using MSSQL17, very much an exploratory piece I am conducting, looking at sales by products and services, and running totals for a month.

Trying to work out how to have a query that sums my products from a transactions table in this instance by date and product ID to recreate something like:

-------------------
Date            | ProductID | Sale Price |  Cumulative_Amount   | 
'2018-09-12'            1           1          1
'2018-09-12'            2           2          2
'2018-09-13'            1           1          2
'2018-09-13'            2           2          4

At the moment I seem to have perfected the art of just having one great big total for the cumulative amount but can't seem to work out how to get this to reset as such for each product. Product and service costs, may change and wont always be static as above.

I can manually manipulate and look in excel etc but would quite like to know how this is possible in SQL to save myself time in the future, any tips or pointers would be very much appreciated.

At the moment I have the following:

SELECT
    [TransactionDate],
    [ProductID],
    [SalePrice],
    SUM (ActualSale) over (order by [TransactionDate],[ProductID] rows unbounded preceding) as Cumulative_Amount,    
FROM tTransactions
    WHERE [TransactionDate] BETWEEN '2018-09-10' AND '2018-09-15'

Thank you in advance.

Upvotes: 0

Views: 99

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You need partition by:

SELECT . . . ,
       SUM(ActualSale) OVER (PARTITION BY ProductId
                             ORDER BY TransactionDate
                            ) as Cumulative_Amount,
FROM tTransactions
WHERE TransactionDate BETWEEN '2018-09-10' AND '2018-09-15';

Note that the windowing clause is not needed. It defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when not present. This can be a bit misleading if you have duplicate dates. If that is an issue, then you an explicitly include the window frame:

SELECT . . . ,
       SUM(ActualSale) OVER (PARTITION BY ProductId
                             ORDER BY TransactionDate
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                            ) as Cumulative_Amount,
FROM tTransactions
WHERE TransactionDate BETWEEN '2018-09-10' AND '2018-09-15';

Upvotes: 3

Related Questions