SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Combining COUNT(*) features
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlconfused
Starting Member

Canada
43 Posts

Posted - 11/19/2012 :  11:58:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  12:09:31  Show Profile  Reply with Quote
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
Go to Top of Page

sqlconfused
Starting Member

Canada
43 Posts

Posted - 11/19/2012 :  12:36:41  Show Profile  Reply with Quote
Thank you. I have no idea what COALESCE does (will Google it after) but it worked perfect.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  12:47:37  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000