Using SUM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using SUM

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2012-07-06 : 18:54:43

I am trying to get the SUM of the GROSS_INV_AMT which is in the INV table. I only want to SUM if the INV_LINE.PMT_PRICE minus the INV_LINE.PO_PRICE is +/- 50.00.

The problem I am having is that there are multiple INV_LINES for each INV. When I SUM the GROSS_INV_AMT from the INV table, it gets summed for each INV_LINE which makes my total dollars completely out of whack.

My latest attempt at trying to get the correct dollar amount produced the following error:

"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

I am not sure where to go from here. Any ideas? Here is my code:

declare @LoDate as DATETIME
declare @HiDate as DATETIME

set @LoDate = '06/01/2012'
set @HiDate = '06/30/2012'

SELECT
SUM(CASE
WHEN ABS(PMT_PRICE-PO_PRICE) < 50
THEN SUM(INV_LINE.PMT_PRICE) END) 'TOTAL DOLLARS WITHIN TOLERANCE'

FROM INV
JOIN INV_LINE
ON INV_LINE.INV_ID = INV.INV_ID and
INV_LINE.INV_IDB = INV.INV_IDB

where
INV_LINE.STAT <> 12
and INV.REC_UPDATE_DATE >= @LoDate
and INV.REC_UPDATE_DATE <= @HiDate
and ABS(INV_LINE.PMT_PRICE - INV_LINE.PO_PRICE) < 50

SLReid
Forum Newbie
Renton, WA USA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 23:13:33

[code]
SELECT
SUM(CASE
WHEN ABS(PMT_PRICE-PO_PRICE) < 50
THEN INV_LINE.PMT_PRICE ELSE 0 END) AS 'TOTAL DOLLARS WITHIN TOLERANCE'

FROM INV
JOIN INV_LINE
ON INV_LINE.INV_ID = INV.INV_ID and
INV_LINE.INV_IDB = INV.INV_IDB

where
INV_LINE.STAT <> 12
and INV.REC_UPDATE_DATE >= @LoDate
and INV.REC_UPDATE_DATE <= @HiDate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2012-07-07 : 14:49:13

Hmmm... I tried that and I am still getting the INV.GROSS_INV_AMT overstated. I changed your query a little because I am trying to get the INV.GROSS_INV_AMT which is at the header level vs the line level. Because there are more lines than headers it appears to be counting each INV header multiple times. Any other suggestions?

SELECT
SUM(CASE
WHEN ABS(PMT_PRICE-PO_PRICE) < 50
THEN INV.GROSS_INV_AMT ELSE 0 END) AS 'TOTAL DOLLARS WITHIN TOLERANCE'

FROM INV
JOIN INV_LINE
ON INV_LINE.INV_ID = INV.INV_ID and
INV_LINE.INV_IDB = INV.INV_IDB

where
INV_LINE.STAT <> 12
and INV.REC_UPDATE_DATE >= @LoDate
and INV.REC_UPDATE_DATE <= @HiDate

SLReid
Forum Newbie
Renton, WA USA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-07 : 16:36:55

to help further i need to know how your data is. can you post some sample data and explain what your required output is?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources