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.
ginsburg7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajkumar GsSoftware EngineerCommented:
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

0
Rajkumar GsSoftware EngineerCommented:
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
0
ginsburg7Author Commented:
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
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Rajkumar GsSoftware EngineerCommented:
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

0
TyugCommented:
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
0
Rajkumar GsSoftware EngineerCommented:
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

0
ginsburg7Author Commented:
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.
0
ginsburg7Author Commented:
I figured it out myself.  I had to add a unique field (ID), then this worked:
SELECT     ID, DAY, [ORDER], AMT,
                          (SELECT     SUM(AMT) AS Expr1
                            FROM          RJM_Commissions_SO_Bookings AS RJM_Commissions_SO_Bookings_1
                            WHERE      (ID <= RJM_Commissions_SO_Bookings.ID)) AS 'Running Total'
FROM         RJM_Commissions_SO_Bookings
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.