adapting running totals to my query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

adapting running totals to my query

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-07-08 : 08:22:35

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-08 : 08:25:59

[code]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[/code]

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-07-08 : 10:32:05

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-08 : 10:49:02

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;

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-07-08 : 11:03:40

Cool that works for me and SQL 2008, beside that I learned how to use this sort of alias!
Thanks again,
Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources