SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using SUM		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sherrireid
Yak Posting Veteran

USA
50 Posts
Posted - 07/06/2012 :  18:54:43  Show Profile  Reply with Quote
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

India
47144 Posts
Posted - 07/06/2012 :  23:13:33  Show Profile  Reply with Quote 

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

sherrireid
Yak Posting Veteran

USA
50 Posts
Posted - 07/07/2012 :  14:49:13  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47144 Posts
Posted - 07/07/2012 :  16:36:55  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000