asked on

SQL running total

I have successfully created a running total field that adds all the sales orders for the day to the previous day's running total. I am unable to get a second query to add the total for each order (several per day) to the previous running total. So I want a running total by order, not day.

This is what worked:
(SELECT SUM(EXT_PRICE) AS Expr1
FROM dbo.RJM_Commissions_SO_Bookings AS RJM_Commissions_SO_Bookings_1
WHERE (CONVERT (varchar(20), CONVERT (datetime, BOOKING_DATE), 111) <=
CONVERT (varchar(20), CONVERT (datetime, RJM_Bookings.BOOKING_DATE), 111)))

This is what is not working. It gives me the grand total on all records:
(SELECT SUM(EXT_PRICE) AS Expr1
FROM dbo.RJM_Commissions_SO_Bookings AS RJM_Commissions_SO_Bookings_1
WHERE ORDER <= RJM_Commissions_SO_Bookings_1.ORDER )

Do I need a date field for running totals to work?

Thank you.

Rajkumar Gs

I think the query will look like this

Raj

SELECT SUM(EXT_PRICE) AS Expr1
FROM RJM_Commissions_SO_Bookings 
WHERE [ORDER] < (SELECT MAX([ORDER]) FROM RJM_Commissions_SO_Bookings)

Open in new window

Rajkumar Gs

It will sum up all the orders till previous order. I am not sure whether I understood your question right.

Is this you are looking for ?

Raj

ginsburg7

ASKER

No, that is not it. I'll explain with an example:

This is what I want with my second query but cannot figure out:
Day Order Amt Running Total
1 1 5 5
1 2 6 11
2 1 7 18
2 2 3 21

This is what I can get with my first query:
Day Order Amt Running Total
1 1 5 11
1 2 6 11
2 1 7 21
2 2 3 21

Rajkumar Gs

Check the scripts

Raj

-- ORDER WISE
WITH TABLECTE AS
(
	SELECT DAY(BOOKING_DATE) AS [Day], [Order], EXT_PRICE AS Amt, DENSE_RANK() OVER(ORDER BY DAY(BOOKING_DATE), [ORDER]) AS SERIAL
	FROM RJM_Commissions_SO_Bookings
)
SELECT *, (SELECT SUM(Amt) FROM TABLECTE WHERE SERIAL <= T.SERIAL) FROM TABLECTE T

-- DATE WISE
WITH TABLECTE AS
(
	SELECT DAY(BOOKING_DATE) AS [Day], [Order], EXT_PRICE AS Amt, ROW_NUMBER() OVER(ORDER BY DAY(BOOKING_DATE)) AS SERIAL
	FROM RJM_Commissions_SO_Bookings
)
SELECT *, (SELECT SUM(Amt) FROM TABLECTE WHERE SERIAL <= T.SERIAL) FROM TABLECTE T

Open in new window

Tyug

This should work according to your sample given.

select a.Day, a.Order, a.Amt, sum(a.Amt) as Running Total
from Table a cross join Table b
where b.Day <= a.Day
group by a.Id, a.Name , a.Amt

Rajkumar Gs

Slight modification including column name alias and required fields only as you shown in the expected output.

Raj

-- ORDER WISE
WITH TABLECTE AS
(
	SELECT DAY(BOOKING_DATE) AS [Day], [Order], EXT_PRICE AS Amt, DENSE_RANK() OVER(ORDER BY DAY(BOOKING_DATE), [ORDER]) AS SERIAL
	FROM RJM_Commissions_SO_Bookings
)
SELECT [Day], [Order], Amt, (SELECT SUM(Amt) FROM TABLECTE WHERE SERIAL <= T.SERIAL)  AS [Running Total]
FROM TABLECTE T

-- DATE WISE
WITH TABLECTE AS
(
	SELECT DAY(BOOKING_DATE) AS [Day], [Order], EXT_PRICE AS Amt, ROW_NUMBER() OVER(ORDER BY DAY(BOOKING_DATE)) AS SERIAL
	FROM RJM_Commissions_SO_Bookings
)
SELECT [Day], [Order], Amt, (SELECT SUM(Amt) FROM TABLECTE WHERE SERIAL <= T.SERIAL) AS [Running Total]
FROM TABLECTE T

Open in new window

ginsburg7

ASKER

RajkumarGS, I was able to replicate your work and it does work. But I have a problem - I need to be able to use this in SQL Server Management Studio and it says that it cannot represent the statement in the grid pane or the diagram pane. I'm a beginner with SQL and benefit greatly by being able to see all the panes. Is there a way to modify this so I can use this?

Tyua, you statement resulted in the wrong totals. I couldn't figure out what was going on so I cannot give you any guidance.

Thank you both.

ASKER CERTIFIED SOLUTION

ginsburg7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial