CODEWITHSUNDEEP
sqlsql-server
Sankar Narayanan
Sankar Narayanan

Reputation: 23

Running total based on multiple conditions

I have a table with columns, Year, Week, Market, Product Sale. My running total should be

I have tried the SQL script over(order by year, Week, Market, Product ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). This works fine only for the first record set of Market & product combination. Soon as the market and/or Product change the output still computes considering the last updated data.

Year    Week    Market  Product  Sale   RunningTotal
2017    1       USA     Shampoo  100    100
2017    2       USA     Shampoo  100    200
2017    3       USA     Shampoo  100    300
2017    1       USA     Soap     100    400
2017    2       USA     Soap     100    500
2017    3       USA     Soap     100    600
2017    1       India   Soap     200    800
2017    2       India   Soap     200    1000
2017    3       India   Soap     200    1200

The result I am looking for is this

Year    Week    Market  Product  Sale   RunningTotal
2017    1       USA     Shampoo  100    100
2017    2       USA     Shampoo  100    200
2017    3       USA     Shampoo  100    300
2017    1       USA     Soap     100    100
2017    2       USA     Soap     100    200
2017    3       USA     Soap     100    300
2017    1       India   Soap     200    200
2017    2       India   Soap     200    400
2017    3       India   Soap     200    600

Upvotes: 2

Views: 689

Answers (2)

Sreenu131
Sreenu131

Reputation: 2516

i tried using sum()Over() Function

;With cte([Year],[Week], Market,Product,Sale)
AS
(
SELECT 2017,1,'USA'  ,  'Shampoo',100 Union all
SELECT 2017,2,'USA'  ,  'Shampoo',100 Union all
SELECT 2017,3,'USA'  ,  'Shampoo',100 Union all
SELECT 2017,1,'USA'  ,  'Soap'   ,100 Union all
SELECT 2017,2,'USA'  ,  'Soap'   ,100 Union all
SELECT 2017,3,'USA'  ,  'Soap'   ,100 Union all
SELECT 2017,1,'India',  'Soap'   ,200 Union all
SELECT 2017,2,'India',  'Soap'   ,200 Union all
SELECT 2017,3,'India',  'Soap'   ,200 
)
SELECT [Year],
        [Week],
        Market,
        Product,
        Sale,
        SUM(Sale)OVER(PARTITION BY Sale,Product ORDER BY RNo ) AS RunningTotal
FROm
(
SELECT *, ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS RNo from cte
)dt

Expected Result

Year    Week    Market  Product  Sale   RunningTotal
2017    1       USA     Shampoo  100    100
2017    2       USA     Shampoo  100    200
2017    3       USA     Shampoo  100    300
2017    1       USA     Soap     100    100
2017    2       USA     Soap     100    200
2017    3       USA     Soap     100    300
2017    1       India   Soap     200    200
2017    2       India   Soap     200    400
2017    3       India   Soap     200    600

Upvotes: 0

TT.
TT.

Reputation: 16145

You need to partition by the market and product:

OVER (
    PARTITION BY Market, Product 
    ORDER BY year, Week 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Upvotes: 4

Related Questions