Need help with this sql grouping/aggregate query

[glui2001]

Hi,

I have a query that gives this table


----------------|
count | user |
----------------|
1 | sam |
----------------|
13 | sam |
----------------|
1 | pat |
----------------|
7 | gus |
----------------|
1 | art |
----------------|

For this discussion, let's say that it lists the count of some units that each user has.

The query for this is simple enuff.

select count(count), user
from <table>
group by user;

Now what I want now is a display that shows the number of users that have the same unit count value. So the table would look like this.

--------------|
count | u_count |
--------------|
1 | 3 |
--------------|
13 | 1 |
--------------|
7 | 1 |
--------------|


Is there a way to use sql only to accomplish this?

TIA,
george

 

[rtmpxr]

Create a view based on the first SQL and then query the view with the second one. You can use temporary table also, if the SQL engine has this feature.

 

[BML]

Try this:

Select Count As Count1, Count(User)
From Table
Group by Count1

Hope this helps!