count on one column and group by another column

Question

Suppose that we have a sample table

c1    |    c2
--------------
a     |     b
a     |     b
a     |     c
a     |     c
a     |     c
d     |     e
d     |     e

How can we turn this table into the following format

c1    |    c2   |     c3
--------------------------
a     |     b   |     2
a     |     c   |     3
d     |     e   |     2

where c3 contains the count of c2 based on distinct c1 value.

Answer 1

This is a group by with two keys:

select c1, c2, count(*)
from t
group by c1, c2;

Answer 2

select c1, c2, count(*) as c3
from your_table
group by c1, c2