Unique Count by id

Question

How would i do a unique count, so that the count for ID # 1: would be Green - 2, Red - 1, Blue - 1 and for ID#2, Orange - 2, Pink - 1, Blue - 1, White - 1.

Then I need to rank the colors using the RANK Function by ID. I've seen online how to use the rank function.

I am using sql server 2014.

Answer 1

I think the query you are looking for is:

select id, color, count(*) as cnt,
       rank() over (partition by id order by count(*) desc) as rnk
from t
group by id, color;