Combining COUNT(*) features - 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

General SQL Server Forums

New to SQL Server Programming

Combining COUNT(*) features

Author

Topic

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2012-11-19 : 11:58:58

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 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 & "'"

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 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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources