SQL- Do I need some kind of Count function?

Question

I need to query a database that has about 10-11 columns, including a column of id's and a column of role codes. Those are the 2 column that i'm interested in.

ID    ROLE
1     a
2     a
2     b
2     c
3     a
4     a
4     b

I need to count how many role codes exist for each ID. (Basically like counting the number of times each id exists in the database)

Output should be something like this:

ID     Count
1      1
2      3
3      1
4      2

Answer 1

Use count distinct:

SELECT ID, COUNT(DISTINCT ROLE) 
   FROM YOURTABLE
 GROUP BY ID