CODEWITHSUNDEEP
sqlsql-serversql-server-2008
euge1220
euge1220

Reputation: 235

SQL count distinct values of another value

say i have a table that looks like this column names (cusip_nbr, partc_nbr) lets called the table title table_cusip (sorry don't know how to format btw)

cusip_nbr ----- partc_nbr

00162Q106 ------------ 0756
00162Q106 ------------ 0231
00162Q106 ------------ 0756
00162Q106 ------------ 0231
231292106 ------------ 0412
231292106 ------------ 0395
231292106 ------------ 0101
231292106 ------------ 0291
43129U101 ------------ 0756
43129U101 ------------ 0395
43129U101 ------------ 0921
43129U101 ------------ 0756

What SQL code can i write to return a table like (basically to look at the cusip_nbr and see how many distinct participant numbers there are per cusip_nbr)? -> 00162Q106 has 2 distinct members, 231292106 has 4, and 43129U101 has 3. (This is just a snip of my table i have probably 1,300 cusip_nbrs that i need to count members for)

cusip_nbr ---- nbr_of_members

00162Q106 ----------------- 2
23129106 --------------------4
43129U101------------------ 3

Upvotes: 2

Views: 331

Answers (2)

Jafar Kofahi
Jafar Kofahi

Reputation: 763

You need to do select with group-by

Select cusip_nbr, count(distinct partc_nbr)
from table_cusip 
group by cusip_nbr

Upvotes: 0

juergen d
juergen d

Reputation: 204924

select cusip_nbr, count(distinct partc_nbr) as nbr_of_members
from table_cusip 
group by cusip_nbr

Upvotes: 6

Related Questions