Totals question

Question

Totals question

rjdpa2

SSC Enthusiast

Points: 164
More actions
May 18, 2016 at 1:25 pm

#327157

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)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Y.B. SSChampion Points: 11649 More actions · Answer 2

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,

SELECT quote FROM brain WHERE original = 1
0 rows returned

Y.B. SSChampion Points: 11649 More actions · Answer 3

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

SELECT quote FROM brain WHERE original = 1
0 rows returned

drew.allen SSC Guru Points: 76985 More actions · Answer 4

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

Y.B. SSChampion Points: 11649 More actions · Answer 5

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.

SELECT quote FROM brain WHERE original = 1
0 rows returned