help with 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)

help with query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-25 : 21:42:15

Hi,

I have the following query, which is working perfectly, but I would like to add some extended functionality on to it.

In addition to the "totalPics" column, I want to add a "totalPics1" and "totalPics2". TotalPics will be the sum of these two columns. Each column will represent the associated genderID of the photo.

I can access "tblUserDetails.genderID" in the following manner.

JOIN tblExtraPhotos EP on EP.counterID = FP.counterID
JOIN tblUserDetails UD2 on UD2.userID = EP.userID

Here is the original query


SELECT  TOP 40  FP.userID, count(*) AS totalPics, max(dateAdded) AS m_dateadded, UD.nameOnline
FROM tblfavouritephotos FP

JOIN tblUserDetails UD on UD.userID = FP.userID
GROUP BY FP.userID, nameOnline
ORDER BY m_dateadded desc

Here is it modified to add the JOIN's that I think it needs. It runs fine, but I'm not sure how to select the totalPics1 and totalPics2 columns.

pseudocode:

totalPics1 = count(*) WHERE genderID=1
totalPics2 = count(*) WHERE genderID=2


SELECT  TOP 40  FP.userID, count(*) AS totalPics,       max(dateAdded) AS m_dateadded, UD.nameOnline 
FROM     tblfavouritephotos FP

JOIN tblUserDetails UD on UD.userID = FP.userID


JOIN tblExtraPhotos EP on EP.counterID = FP.counterID
JOIN tblUserDetails UD2 on UD2.userID = EP.userID

GROUP BY FP.userID, UD.nameOnline

ORDER BY m_dateadded desc

Does this make sense? Any help is much appreciated..

Thanks again!
mike123

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-25 : 21:50:46

you can use case statement to do it


sum(case when genderid = 1 then 1 else 0 end) as [Count of Gender1],
sum(case when genderid = 2 then 1 else 0 end) as [Count of Gender2]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-26 : 00:10:35

exactly what I was looking for :)

thanks again Khtan,

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources