Need help with an sql query for creating a statistic in an sql table

I have a table like this:

HOST_ID    SCSI_LUN_ID
92         1
92         1
92         1
80         1
80         17
76         462
76         331
76         464
10         3
10         3

I need a 开发者_运维百科quick sql help that can create output like this:

HOST_ID    HOST_ID_COUNT
92         3
80         2
76         3
10         2

Basically, I want to count all distinct HOST_IDs.

Thanks.

(Using MS Sql Server if that matters)

Select host_id, count(*) 
from   mytable 
group by host_id

Select host_id, distinct count(host_id) as host_id_count from TABLENAMEHERE order by host_id;

You should be able to do:

SELECT HOST_ID, COUNT(SCSI_LUN_ID) FROM TABLENAME GROUP BY HOST_ID

That's for MySQL anyway, but I think group by is standard SQL