September 12, 2012 at 12:00 am
Hi, I have multiple users per account ie
Acc A
User1
User2
User3
Acc B
User4
User5
User6
User7
etc....
I first perform a count(*) of how many users there are per account
my results would be
Acc A|3
Acc B|4
But I have several hundred accounts so now I would like to know how many Accounts have 1 user, how many have 2 users etc
Num_Users_in_Acc|Count
1|560
2|5674
3|63527
etc
Here is my code to get the first part:
select count( users ) as Counts, Acc_no
from Temp_1
where area = 'PMB'
group by Acc_no
Thanks a million
September 12, 2012 at 12:38 am
Probably something like this
SELECTCounts AS Num_Users_in_Acc, COUNT(Acc_no) AS [COUNT]
FROM(
SELECTCOUNT( users ) AS Counts, Acc_no
FROMTemp_1
WHEREarea = 'PMB'
GROUP BY Acc_no
) T
GROUP BY Counts
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 12, 2012 at 12:42 am
Perfect! Thanks a million 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply