drew.allen (5/18/2016)

---

yb751 (5/18/2016)

---

Ok, back from my meeting and thanks to Lowell's for the CTE.

;WITH MyOriginalTable([TransactionID],[LineItemNumber],[Amount])

AS

(

SELECT 1,1,1.00 UNION ALL

SELECT 2,1,2.00 UNION ALL

SELECT 2,2,3.00 UNION ALL

SELECT 2,3,1.00 UNION ALL

SELECT 3,1,4.00 UNION ALL

SELECT 3,2,3.00

)

SELECT

TransactionID,

LineItemNumber,

Amount,

SUM (Amount) OVER (PARTITION BY TransactionID ORDER BY LineItemNumber ROWS BETWEEN UNBOUNDED PRECEDING

AND UNBOUNDED FOLLOWING) AS TransactionTotal

FROM

MyOriginalTable

By default, SUM() sums everything in the partition. You only need to specify the ORDER BY and ROWS/RANGE if you want a running total. This can be written much more simply as

SELECT

TransactionID,

LineItemNumber,

Amount,

SUM (Amount) OVER (PARTITION BY TransactionID) AS TransactionTotal

FROM

MyOriginalTable

If you specify the ORDER BY without a ROWS/RANGE clause, it will use the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

If you specify the ROWS/RANGE, then you MUST specify the ORDER BY.

Drew