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 DESC
This 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 DESC
and
SELECT COUNT(*) as ID from galleryindex where gcreator = '" & creator & "'"
SELECT
a.creator,
a.Totals,
COALESCE(b.ID,0) AS ID
FROM
(
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
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.
Combining COUNT(*) features
Posted - 11/19/2012 : 11:58:58
