SUM function for subquery - 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)

SUM function for subquery

Author

Topic

BlackIce662
Starting Member

11 Posts

Posted - 2013-12-31 : 02:24:59

Hi

I'm trying to get a total for amounts that will come from a subquery.
I'm getting multiple errors and I've tried multiple places for the SUM function but
I'm quite lost now.

Thank you
My code:

SELECT SUM(Amount=ABS((SELECT Top 1 Amount+VATAmount 
FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND FAAccKitty.TransType='PZ')))
FROM FAAccWinners 
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.PlaceID = FAAccWinners.PlaceID) 
INNER JOIN Visitor WITH(NOLOCK) ON (Reservations.EMail = Visitor.EMail) 
WHERE FAAccWinners.Comments_Approved='Yes' 
AND FAAccWinners.PlaceID IN (SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID 
AND FAAccKitty.TransType='PZ' AND Year(TransDate)>1900 AND FAAccKitty.Approved='Yes') AND FAAccWinners.PlaceID NOT IN 
(SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND TransType='PC') 
AND Visitor.VisitorID= 274

Also tried:

SELECT SUM(ABS((SELECT Top 1 Amount+VATAmount 
FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND FAAccKitty.TransType='PZ')))
FROM FAAccWinners 
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.PlaceID = FAAccWinners.PlaceID) 
INNER JOIN Visitor WITH(NOLOCK) ON (Reservations.EMail = Visitor.EMail) 
WHERE FAAccWinners.Comments_Approved='Yes' 
AND FAAccWinners.PlaceID IN (SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID 
AND FAAccKitty.TransType='PZ' AND Year(TransDate)>1900 AND FAAccKitty.Approved='Yes') AND FAAccWinners.PlaceID NOT IN 
(SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND TransType='PC') 
AND Visitor.VisitorID= 274

Tried this as well:

SELECT Amount=ABS((SUM(SELECT Top 1 Amount+VATAmount 
FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND FAAccKitty.TransType='PZ')))
FROM FAAccWinners 
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.PlaceID = FAAccWinners.PlaceID) 
INNER JOIN Visitor WITH(NOLOCK) ON (Reservations.EMail = Visitor.EMail) 
WHERE FAAccWinners.Comments_Approved='Yes' 
AND FAAccWinners.PlaceID IN (SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID 
AND FAAccKitty.TransType='PZ' AND Year(TransDate)>1900 AND FAAccKitty.Approved='Yes') AND FAAccWinners.PlaceID NOT IN 
(SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND TransType='PC') 
AND Visitor.VisitorID= 274

Any help will be greatly appreciated

Greatness... Live It!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-31 : 04:06:40

Something like this?

SELECT		f.PlaceID,
		f.theSum
FROM		(
			SELECT		PlaceID,
					SUM(Amount + VatAmount) AS theSum
			FROM		dbo.FAAccKitty WITH (NOLOCK) 
			GROUP BY	PlaceID
			HAVING		MAX(CASE WHEN TransType = 'PZ' AND TransDate >= '19010101' AND Approved = 'Yes' THEN 1 ELSE 0 END) = 1
					AND MAX(CASE WHEN TransType = 'PC' THEN 1 ELSE 0 END) = 0
		) AS f
INNER JOIN	dbo.FAAccWinners AS w WITH (NOLOCK) ON w.PlaceID = f.PlaceID
			AND w.Comments_Approved = 'Yes' 
INNER JOIN	dbo.Reservations AS r WITH (NOLOCK) ON r.PlaceID = w.PlaceID
INNER JOIN	dbo.Visitor AS v WITH (NOLOCK) ON v.eMail = r.eMail 
			AND v.VisitorID = 274;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

BlackIce662
Starting Member

11 Posts

Posted - 2013-12-31 : 04:53:33

This returns multiple results I need to add values, all positive and get only one result so if the records are

Amount
1
5
4
5
5

the value I need returned is 20

Greatness... Live It!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources