count frequency of all items in tsql

Suppose I have a table, like so:

UserID    Meh    Meh //meh is some column
````````````````````
01        ...    ...
01        ...    ...
03        ...    ...
05        ...    ...
05        ...    ...
01        ...    ...
03        ...    ...

So I want to count how many times each userid appears in this table. I am doing this now:

select UserId from NinjaTable group by UserId 

but its giving me something 开发者_开发问答that I dont know or understand. I want the result to be like so:

UserID    Frequency
```````````````````
01        3
03        2
05        2

you are almost there.

You are grouping correctly, just add the count to select clause.

select UserId, COUNT(*) from NinjaTable group by UserId order by COUNT(*) desc

that should do it really.

edit: thanks to @abe for pointing the order.

Try something like:

select UserId, count(*) as Frequency from NinjaTable group by UserId

Tack on an order by UserId if you want.

Try

select UserID, Count(*) 
from theTable 
group by UserID

I think what you really want to use is the COUNT function:

SELECT UserID, COUNT(*) AS Frequency FROM NinjaTable GROUP BY UserID

You would use the group by statement:

SELECT UserId, COUNT(1) FROM MyTable GROUP BY UserId ORDER BY COUNT(1) DESC

This would group all the user Ids, find the count of rows (the frequency for you), and then sort the result set by the frequency descending.