Create a Column That holds the SUM of values in the same field where The Invoice Number is the same

Question

Post reply

Create a Column That holds the SUM of values in the same field where The Invoice Number is the same

mbrady5

SSCrazy

Points: 2835
More actions
February 1, 2013 at 7:44 am

#270180

First, sorry for the confusing title.
I am looking for a way to sum the values based on the customerid and invoiceid being the same for multiple rows Then adding a column with the sum of all.
SELECT T1.IDCUST, T1.IDINVC, T2.AMTPAYM, 'SumofRows' AS TotalPayment
FROM MyTable1_ARIBH T1 INNER JOIN
MyTable2_ARTCP T2 ON T1.IDCUST = T2.IDCUST AND
T1.IDINVC = T2.IDINVC
WHERE (T1.IDCUST = 'TOR829') AND (T1.IDINVC = '72124')
Results: I would like the TotalPayment Column to be the SUM of AMTPAYM from the 3 rows. Thanks
IDCUSTIDINVCAMTPAYMTotalPayment
TOR829 72124 23815.180
TOR829 72124 72813.500
TOR829 72124 19.070

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

Something like this?

SELECT

T1.IDCUST,

T1.IDINVC,

T2.AMTPAYM,

Dev1.TotalPayment

FROM

MyTable1_ARIBH T1

INNER JOIN

MyTable2_ARTCP T2

ON

T1.IDCUST = T2.IDCUST

AND

T1.IDINVC = T2.IDINVC

INNER JOIN

(

SELECT

T1.IDCUST,

T1.IDINVC,

SUM(T2.AMTPAYM) AS TotalPayment

FROM

MyTable1_ARIBH T1

INNER JOIN

MyTable2_ARTCP T2

ON

T1.IDCUST = T2.IDCUST

AND

T1.IDINVC = T2.IDINVC

WHERE

T1.IDCUST = 'TOR829'

AND

T1.IDINVC = '72124'

GROUP BY

T1.IDCUST,

T1.IDINVC

) AS Dev1

ON

T1.IDCUST = Dev1.IDCUST

AND

T1.IDINVC = Dev1.IDINVC

WHERE

T1.IDCUST = 'TOR829'

AND

T1.IDINVC = '72124'

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Or this:

SELECT

T1.IDCUST,

T1.IDINVC,

T2.AMTPAYM,

TotalPayment = SUM(AMTPAYM) OVER (PARTITION BY IDCUST, IDINVC)

FROM MyTable1_ARIBH T1

INNER JOIN MyTable2_ARTCP T2

ON T2.IDCUST = T1.IDCUST

AND T2.IDINVC = T1.IDINVC

WHERE T1.IDCUST = 'TOR829'

AND T1.IDINVC = '72124'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

ChrisM@Work (2/1/2013)
Or this:
SELECT
T1.IDCUST,
T1.IDINVC,
T2.AMTPAYM,
TotalPayment = SUM(AMTPAYM) OVER (PARTITION BY IDCUST, IDINVC)
FROM MyTable1_ARIBH T1
INNER JOIN MyTable2_ARTCP T2
ON T2.IDCUST = T1.IDCUST
AND T2.IDINVC = T1.IDINVC
WHERE T1.IDCUST = 'TOR829'
AND T1.IDINVC = '72124'

Doh, I keep forgetting you can OVER an aggregate function.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 4

anthony.green (2/1/2013)
ChrisM@Work (2/1/2013)
Or this:
SELECT
T1.IDCUST,
T1.IDINVC,
T2.AMTPAYM,
TotalPayment = SUM(AMTPAYM) OVER (PARTITION BY IDCUST, IDINVC)
FROM MyTable1_ARIBH T1
INNER JOIN MyTable2_ARTCP T2
ON T2.IDCUST = T1.IDCUST
AND T2.IDINVC = T1.IDINVC
WHERE T1.IDCUST = 'TOR829'
AND T1.IDINVC = '72124'
Doh, I keep forgetting you can OVER an aggregate function.

BWAA-HAAAA!!!! I used to OVER look it all the time! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

Jeff Moden (2/1/2013)
anthony.green (2/1/2013)
ChrisM@Work (2/1/2013)
Or this:
SELECT
T1.IDCUST,
T1.IDINVC,
T2.AMTPAYM,
TotalPayment = SUM(AMTPAYM) OVER (PARTITION BY IDCUST, IDINVC)
FROM MyTable1_ARIBH T1
INNER JOIN MyTable2_ARTCP T2
ON T2.IDCUST = T1.IDCUST
AND T2.IDINVC = T1.IDINVC
WHERE T1.IDCUST = 'TOR829'
AND T1.IDINVC = '72124'
Doh, I keep forgetting you can OVER an aggregate function.
BWAA-HAAAA!!!! I used to OVER look it all the time! 😛

Your just to cool for school Jeff

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 6

anthony.green (2/4/2013)
Jeff Moden (2/1/2013)
anthony.green (2/1/2013)
ChrisM@Work (2/1/2013)
Or this:
SELECT
T1.IDCUST,
T1.IDINVC,
T2.AMTPAYM,
TotalPayment = SUM(AMTPAYM) OVER (PARTITION BY IDCUST, IDINVC)
FROM MyTable1_ARIBH T1
INNER JOIN MyTable2_ARTCP T2
ON T2.IDCUST = T1.IDCUST
AND T2.IDINVC = T1.IDINVC
WHERE T1.IDCUST = 'TOR829'
AND T1.IDINVC = '72124'
Doh, I keep forgetting you can OVER an aggregate function.
BWAA-HAAAA!!!! I used to OVER look it all the time! 😛
Your just to cool for school Jeff

Not even close... I'm trying to quit smoking, again, and my jokes are really going to such for about the next 6 months because of it. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ant-Green SSC Guru Points: 113445 More actions · Answer 7

Jeff Moden (2/4/2013)
anthony.green (2/4/2013)
Jeff Moden (2/1/2013)
anthony.green (2/1/2013)
ChrisM@Work (2/1/2013)
Or this:
SELECT
T1.IDCUST,
T1.IDINVC,
T2.AMTPAYM,
TotalPayment = SUM(AMTPAYM) OVER (PARTITION BY IDCUST, IDINVC)
FROM MyTable1_ARIBH T1
INNER JOIN MyTable2_ARTCP T2
ON T2.IDCUST = T1.IDCUST
AND T2.IDINVC = T1.IDINVC
WHERE T1.IDCUST = 'TOR829'
AND T1.IDINVC = '72124'
Doh, I keep forgetting you can OVER an aggregate function.
BWAA-HAAAA!!!! I used to OVER look it all the time! 😛
Your just to cool for school Jeff
Not even close... I'm trying to quit smoking, again, and my jokes are really going to such for about the next 6 months because of it. 😛

Good luck with the stopping, the other half is trying again as well, so I know what your going through.

Sean Lange SSC Guru Points: 286573 More actions · Answer 8

Jeff Moden (2/4/2013)
Not even close... I'm trying to quit smoking, again, and my jokes are really going to such for about the next 6 months because of it. 😛

You can do it Jeff. It is damn hard!!! I have been smoke free for almost 9 years now and was a 2 pack a day smoker for about 25 years. The patch helped me, just take it off before bed.

Sorry to hijack this thread but I wanted to offer my support.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/