Distinct Records with a count

[prouse]

Is it possible to return all distinct records in a column as well as count of each distinct records:

example: I have a column called sID which may contain duplicate ids.

SID COUNT
---- -------
K13R 1
K14R 2
K15R 1
K17T 4


I havent been able to figure how to use the count function with the distinct keyword. I am not sure if that is the right approach anyway.

All assistance will be greatly appreciated.

[chrisrlong]

Not exactly sure what you're asking. Could you please show the result set you would expect using your example?

Thanks,

- Chris

[m1l]

I think you need to use the group by, so it might look something like

select sid, count(*)
from table
group by sid

[Chris]

Hi,

I'm not sure if this is what you're after, but can't you
just use

select distinct col_name, count(*)
group by col_name
/

Cheers

chris.

[Chris]

Exactly, m1l,

you don't need the distinct anyway with the group by.

[sri_garige]

hi,

I think this may solve ur problem

select count(distinct(sid)) from tablename;

its counts the distinct values in a table only