SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 adapting running totals to my query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnabeck
Posting Yak Master

Spain
201 Posts

Posted - 07/08/2013 :  08:22:35  Show Profile  Reply with Quote
I'm trying to adapt a formula for running totals to my Query, have some difficulties in declaring the tables correctly.
Formula:
SELECT Ordinal, QTY, QTY+COALESCE((SELECT SUM(QTY) 
                                   FROM Table b 
                                   WHERE b.Ordinal < a.Ordinal),0) AS RunningTotal
FROM Table a 
ORDER BY Ordinal

Now my Table is rather lengthy and is described by this query:

Select X.Ord, X.QTY
From (Select.... )X

Now in order to adapt the formula for running totals I did:
Select X.Ord, X.QTY, X.QTY+COALESCE((SELECT SUM(X.QTY)
                                     FROM X b
                                     WHERE b.Ord < a.Ord),0) as RunningTotal
From (Select....)X a
Order by X.Ord

It gives my an ncorrect syntax error near a. I guess I miss the (double) declaration of the Table.

Martin

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 07/08/2013 :  08:25:59  Show Profile  Reply with Quote
Select X.Ord, X.QTY, X.QTY+COALESCE((SELECT SUM(b.QTY)
                                     FROM X b
                                     WHERE b.Ord < X.Ord),0) as RunningTotal
From (Select....)X
Order by X.Ord

Edited by - James K on 07/08/2013 08:26:25
Go to Top of Page

barnabeck
Posting Yak Master

Spain
201 Posts

Posted - 07/08/2013 :  10:32:05  Show Profile  Reply with Quote
Thank you, James. I first was adapting without really understanding; now that I do understand it, I'm even more confused that it still doesn't work:

Invalid object name 'X'. As soon as I add the COALESCE part it starts bothering about the 'X' object, which doesn't make sense to me, because if in the same select expression X.Org and X.QTY is fine, what's wrong with the bracket expression?

Martin
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 07/08/2013 :  10:49:02  Show Profile  Reply with Quote
My mistake. I don't think you can use that alias in the subquery in the from clause. So you will have to repeate the (SELECT ....) query there again. If you want to repeat the lengthy query, make that into a CTE like this:
;WITH cte AS (SELECT ....)
Select X.Ord, X.QTY, X.QTY+COALESCE((SELECT SUM(b.QTY)
                                     FROM cte b
                                     WHERE b.Ord < X.Ord),0) as RunningTotal
From cte X
Order by X.Ord
Below is an example and couple of ways to calculate running total. However, each of these is going to be slow if you have a large number of rows. If you are on SQL 2012 there are easier ways:
CREATE TABLE #tmp(ord INT, qty INT);

INSERT INTO #tmp VALUES (1,10),(3,7),(5,2),(6,1);

SELECT
	x.ord,
	x.qty,
	y.RunningTotal
FROM
	#tmp x
	CROSS APPLY
	(
		SELECT SUM(b.Qty) RunningTotal
		FROM #tmp b
		WHERE b.ord <= x.ord
	)y


SELECT
	x.ord,
	x.qty,
	(SELECT SUM(b.Qty) FROM #tmp b WHERE b.ord <= x.ord) AS RunningTotal
FROM
	#tmp x
ORDER BY
	x.ord;
drop table #tmp;
Go to Top of Page

barnabeck
Posting Yak Master

Spain
201 Posts

Posted - 07/08/2013 :  11:03:40  Show Profile  Reply with Quote
Cool that works for me and SQL 2008, beside that I learned how to use this sort of alias!
Thanks again,
Martin
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000