May 18, 2016 at 1:25 pm
Hi
I have a dataset i used from a sql query i built. It has 3 columns.. See below for Sample in comma seperated values.
Basically I need to add a column to the dataset called "TransactionTotal" and add up the line items that are grouped within a TransactionID. Hopefully my sample below will give a clear picture. What would be the best way to put the total there? Should I use a temp table and add the data that way?
TransactionID,LineItemNumber,Amount,TransactionTotal (new column)
1,1,1.00,x (x should = 1.00)
2,1,2.00,x (x should = 6.00)
2,2,3.00,x (x should = 6.00)
2,3,1.00,x (x should = 6.00)
3,1,4.00,x (x should = 7.00)
3,2,3.00,x (x should = 7.00)
May 18, 2016 at 1:37 pm
becuase you want the total inline with details, you need your same data twice...once grouped by TransacitonId, and the original data.
here's your data as an example: the CTE at the top is just so i have sample data;
;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 T1.*,T2.* FROM MyOriginalTable T1
INNER JOIN (SELECT
[TransactionID],
SUM([Amount]) As TotalAmount
FROM MyOriginalTable
GROUP BY [TransactionID]) T2
On T1.[TransactionID] = T2.[TransactionID]
Lowell
May 18, 2016 at 1:41 pm
This is easily done grouping your rows using a window function. Look up SUM OVER() and see. If you still need some help I'll post back with an example in a few.
Cheers,
May 18, 2016 at 1:54 pm
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
May 18, 2016 at 2:30 pm
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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 18, 2016 at 6:00 pm
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
Thanks Drew, I completely forgot about the default behaviour when using ORDER BY. I knew it didn't feel right...I was like I don't want a running total so I forced it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply