April 16, 2009 at 8:27 am
I have the following, slightly simplified, query:
SELECT
year(subscription_date) as Year_Purchased,
count(sub_id) as Total_Purchases
FROM tblPurchases P
GROUP BY Year(subscription_date)
Which returns results like:
Year_Purchased Total_Purchases
2005 150
2006 400
2007 490
2008 580
Now, I want to add an additional column which is a cumilative record of these purchases, i.e. using the above example data:
Year_Purchased Total_Purchases Overall Total
2005 150 150
2006 400 550
2007 490 1,040
2008 580 1,620
I googled this and found the following:
http://www.devx.com/tips/Tip/15042
Which I tried and couldn't get to work, or understand.
A colleague said I could just run the above and put in a blank column, which could be populated via an update, however, it seems likethe kind of thing which can be acomplished without this...
any advice?!
Thanks.
April 16, 2009 at 8:32 am
There are a number of ways to get running totals. Normally, it's easiest to do it in the application, not in the database, but if you need to do it in the database, search this site for "running totals" and you'll find a couple of good ways to do it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2009 at 8:36 am
Hi
Check out the below link
This has somany ways to do this.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6027
April 16, 2009 at 9:08 am
Hmm based on http://www.sqlteam.com/article/calculating-running-totals
this should work, alas, it does not!
SELECT
year(subscription_date) as Year_of_Purchase,
count(sub_id) as Total_Purchases
INTO tmpTest
FROM tblpurchases p
GROUP BY Year(subscription_date)
SELECT a.Year_of_Purchase, a.total_purchases, sum(b.total_purchases)
FROM tmpTest a
CROSS JOIN tmpTest b
WHERE (b.Year_of_Purchase <= a.Year_of_Purchase) as Running_Totals
GROUP BY a.Year_of_Purchase, a.total_purchases
ORDER BY a.Year_Of_Purchase, a.total_purchases
April 16, 2009 at 9:20 am
are you going to execute this query in Front-End application or any Reporting system?
April 16, 2009 at 9:23 am
Check out the below query
SELECT a.Year_Purchased, a.total_purchases, sum(b.total_purchases)
FROM TT a
CROSS JOIN TT b
WHERE (b.Year_Purchased <= a.Year_Purchased)
GROUP BY a.Year_Purchased, a.total_purchases
ORDER BY a.Year_Purchased, a.total_purchases
April 16, 2009 at 9:26 am
forgot to post the table structure:
CREATE TABLE TT
(Year_Purchased NUMBER, Total_Purchases NUMBER)
INSERT INTO TT VALUES(2005,150);
INSERT INTO TT VALUES(2006,400);
INSERT INTO TT VALUES(2007,490);
INSERT INTO TT VALUES(2008,580);
April 16, 2009 at 9:31 am
I know of no technique that can produce running totals without using a temporary table. Essentially, you do an update assigning a variable value to a column to an expression to keep the running total like this:
update tableA
set @runningTotal = colB = @runningTotal+colB
There is a little more to it than that, because a simple UPDATE is not guaranteed to update all rows in the correct sequence. Lynn Pettis' article on how to handle these issues can be found at:
http://www.sqlservercentral.com/articles/T-SQL/65522/[/url]
Look for the section entitled "Non-partitioned Table Recap" for sample code.
The original article on which it is based is currently being re-written, but this will show you how to ensure you make the updates in the expected sequence.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 16, 2009 at 9:42 am
Thanks Vijaya Kadiyala, that worked fine. I think my example I posted was essentially the same except the wierd bit:
WHERE (b.Year_of_Purchase <= a.Year_of_Purchase) as Running_Totals
according to the original article:
SELECT a.DayCount,
a.Sales,
SUM(b.Sales)
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Oddness.
Thanks for the extra advice bob, I'll be sure to check it out.
Oh, and for the record, no this is not being used in a report or a front end, simply copied from management studio into Excel.
Thanks people!
April 16, 2009 at 9:46 am
Hi
Another way would be a CROSS APPLY to select the totals since the current year:
DECLARE @t TABLE (P_Year DATETIME, P_Purchases MONEY)
INSERT INTO @t
SELECT '2005-01-01', 150
UNION ALL SELECT '2006-01-01', 200
UNION ALL SELECT '2006-02-01', 200
UNION ALL SELECT '2007-01-01', 400
UNION ALL SELECT '2007-10-12', 90
UNION ALL SELECT '2008-01-01', 300
UNION ALL SELECT '2008-02-03', 280
SELECT DATEPART(YEAR, t.P_Year), SUM(t.P_Purchases), t2.Total_Purchases
FROM @t t
CROSS APPLY (SELECT SUM(P_Purchases) Total_Purchases
FROM @t
WHERE DATEPART(YEAR, P_Year) <= DATEPART(YEAR, t.P_Year)
) t2
GROUP BY DATEPART(YEAR, t.P_Year), t2.Total_Purchases
Greets
Flo
April 16, 2009 at 10:11 am
1. You're posting it into Excel? Why not just do the running total's there? It takes about two seconds!!
2. Although cross-apply works, it seems like it would get slower and slower as the size of your resultset increases. I should have said use of UPDATE is the only efficient technique I know of. However, that's an assumption (untested) on my part, so off to the lab I go.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 16, 2009 at 10:14 am
Because I wanted to know how to do it in SQL - it will be a useful thing to know for something else I have coming up, and something else I have done previously which I could modify.
But yes, it would be much quicker in Excel, don't get me started on the stuff I've been reading about SQL Pivot lol.
April 16, 2009 at 10:17 am
Its a lerning curve for me also 🙂 to get my hands on SQL!!!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply