# Calculate distinct value_1 when value_2, value_3 is equal to certain value

The output table looks like this let's say:

``````table : people
+-------+-----------+-----------+
|   id  |  label    |   action  |
+-------+-----------+-----------+
|   1   |  aaaaa    |       B   |
|   1   |  aaaaa    |       B   |
|   1   |  aaaaa    |       A   |
|   2   |  aaaaa    |       B   |
|   2   |  aaaaa    |       B   |
+-------+-----------+-----------+
``````

What I would like to do is to count unique ids this way:

``````table : people
+-------+-----------+-------------+-------------+
|   id  |  label    |   action_A  |   action_B  |
+-------+-----------+-------------+-------------+
|   1   |  aaaaa    |       1     |       1     |
|   2   |  aaaaa    |       0     |       1     |
+-------+-----------+-------------+-------------+
``````

Just use conditional aggregation:

``````select id, label,
sum(case when label = 'A' then 1 else 0 end) as a,
sum(case when label = 'B' then 1 else 0 end) as b
from t
group by id, label;
``````

You can do conditional aggregation:

``````select id, label,
count(distinct case when action = 'A' then action end) as action_a,
count(distinct case when action = 'B' then action end) as action_b
from t
group by id, label;
``````

OR You can just use `max()` :

``````select id, label,
max(case when action = 'A' then 1 else 0 end) as action_a,
max(case when action = 'B' then 1 else 0 end) as action_b
from t
group by id, label;
``````