nested query - 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 2005 Forums

Transact-SQL (2005)

nested query

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2009-08-11 : 05:32:49

SELECT col1,col2,
(SELECT SUM(amt) from tblA
WHERE ID=tblB.ID AND
((Gender='M') OR Gender='F') AND
((Country='LA') OR Country='EU')) AS total
from tblB

now like to include in summation if country='CC' and Gender='M'
if gender='F' ,country='CC' wont be included in sum.

any help pls !
thanks.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-11 : 05:43:54

try this
SELECT col1,col2,(SELECT SUM(amt) from tblA WHERE ID=tblB.ID AND
(((Gender='M' OR Gender='F') AND(Country='LA' OR Country='EU')) OR (Gender = 'M' AND Country ='CC')) AS total
from tblB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-11 : 05:44:41

[code]SELECT b.Col1,
b.Col2,
a.Amt
FROM TblB AS b
LEFT JOIN (
SELECT ID,
SUM(Amt) AS Amt
FROM TblA
WHERE Gender IN ('M', 'F')
AND Country IN ('LA', 'EU')
OR (Gender = 'M' AND Country = 'CC')
GROUP BY ID
) AS a ON a.ID = b.ID[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources