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.OrdBelow 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;
adapting running totals to my query
Posted - 07/08/2013 : 08:22:35
