Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
Hi.I am trying to list the top 10 members that have uploaded the most locations to my site. Currently I am using this string:SELECT top 10 creator, COUNT(*) AS Totals from sites GROUP BY creator ORDER BY Totals DESCThis retrieves the top 10 records from the database of locations - where 'creator' is the username and then "Totals" is retrieved via count(*). The results are grouped by the members names and sorted from highest count to lowest.I then loop through until end of recordset and print out the creator and the associated total for that creator.Eg: Joe - 45 locations, Tom - 40 locations, Bob - 22 locations.I would like to add a second feature which would indicate how many photo galleries the creator has made. This is currently done using this query:SELECT COUNT(*) as ID from galleryindex where gcreator = '" & creator & "'"So what I am doing is as I loop through the top 10, I perform a secondary query where the creator (member's name) is counted from the galleryindex table.Is there a way to combine:SELECT top 10 creator, COUNT(*) AS Totals from sites GROUP BY creator ORDER BY Totals DESCandSELECT COUNT(*) as ID from galleryindex where gcreator = '" & creator & "'"Thank you.
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts
Posted - 2012-11-19 : 12:09:31
One way:
SELECT a.creator, a.Totals, COALESCE(b.ID,0) AS IDFROM ( SELECT top 10 creator, COUNT(*) AS Totals from sites GROUP BY creator ORDER BY Totals DESC ) a OUTER APPLY ( SELECT COUNT(*) AS ID FROM galleryindex b WHERE b.gcreator = a.creator ) b
sqlconfused
Yak Posting Veteran
50 Posts
Posted - 2012-11-19 : 12:36:41
Thank you. I have no idea what COALESCE does (will Google it after) but it worked perfect.
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts
Posted - 2012-11-19 : 12:47:37
COALESCE returns first non-null value from the parameters. I added that only to show ID as zero rather than null if there were no galleries for a creator.