[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL running total

Posted on 2010-03-30
8
Medium Priority
?
880 Views
Last Modified: 2012-05-09
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.
0
Comment
Question by:ginsburg7
  • 4
  • 3
8 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 29106295
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 29106568
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
 

Author Comment

by:ginsburg7
ID: 29107452
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 29111864
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
 
LVL 3

Expert Comment

by:Tyug
ID: 29111871
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 29112154
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
 

Author Comment

by:ginsburg7
ID: 29135772
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
 

Accepted Solution

by:
ginsburg7 earned 0 total points
ID: 29140456
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

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question