Sample Header Ad - 728x90

How do I show a running total from the previous records?

1 vote
1 answer
30 views
I wonder if someone can please assist. I have the following code which basically inserts 10 records into a table. From this table I'm trying to show a running total for the amount of sales against the seller. I've tried using the LAG function but that does not appear to work. Here is the code.
TABLE IF EXISTS RunningTotals 

CREATE TABLE Sales 
(
	OrderId	Int NOT NULL,
	Seller	varchar(10) NULL,
	Amount	money NULL
)

INSERT INTO Sales VALUES
	(1, 'Alex', 10.00),
	(2, 'Sarah', 15.00),
	(3, 'Tracy', 10.25),
	(4, 'Pete', 10.25),
	(5, 'Tracy', 11.00),
	(6, 'Tracy', 10.25),
	(7, 'Alex', 10.25),
	(8, 'Jane', 10.25),
	(9, 'Alex', 20.55)

SELECT Seller, Amount, LAG(Amount,1) OVER (PARTITION BY Seller ORDER BY Seller) AS RunningTotal FROM Sales
Essentially in this example I would expect the following output Seller|Amount|Running Total Alex|10.00|10.00 Alex|1.25|20.25 Alex|20.55|40.90 Jane|10.25|10.25 Pete|10.25|10.25 Sarah|15.00|15.00 Tracy|10.25|10.25 Tracy|11.00|21.25 Tracy|10.25|31.50 Hope someone can help and thanks in advance. Dave
Asked by Dave (25 rep)
Jan 10, 2021, 08:01 PM
Last activity: Jan 10, 2021, 08:17 PM