help with query, group by ? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

help with query, group by ?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-03-26 : 04:52:12

Hi,

I have a table as structured below. Each user can have up to 50 "hotUserID"'s in each users hotlist. (This is limited by doing a check before inserting). How can I run a query to display the breakdown of the userCounts with the amount of users in their hotlist. [did that make sense? please refer to sample data ;)] This max is currently set to 100.

For example I want data sorted by the highest count down.

Example desired results:

hotlistCount, numberOfUsers

100, 4234
99, 2200
98, 1465
97, 1111

Thanks very much for any help!

CREATE TABLE [dbo].[tblHotList] (
[UserID] [int] NOT NULL ,
[HotUserID] [int] NOT NULL
) ON [PRIMARY]
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-26 : 04:55:40

You want a list of the most "wanted"/"hottest" persons? The persons on most peoples lists?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-26 : 04:58:21

You have limited the list to 50, right? How come there is 100 in the example?

select hotlistCount, COUNT(*) AS numberOfUsers FROM (
SELECT COUNT(*) AS hotlistCount, UserID FROM [dbo].[tblHotList] GROUP BY UserID
) AS x GROUP BY hotlistCount ORDER BY 1 DESC, 2

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-03-26 : 05:03:09

Hey Peso,

sorry my mistake, its 2am here.. the 50 was supposed to be 100.

It looks like this query is returning exactly the way I want it to. I just have to verify the data is correct.

Thanks very much once again!!

mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-03-26 : 05:05:32

oh and just to clarify.. no its not a list of the "most popular" peopel.

It's supposed to be a list that shows me how many people have their hotlists full, vs half full etc. I want to see how many people the limitations for maximum people on your list is affecting. (are 50% of the people using this feature, running into the sorry you have maxed out your list message, or is it just 1% of them)

Thanks again!
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources