Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlconfused
Yak Posting Veteran

Canada
50 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
Yak Posting Veteran

Canada
50 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  
 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.06 seconds. Powered By: Snitz Forums 2000