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     |
+-------+-----------+-------------+-------------+

2 answers

  • answered 2020-02-19 11:23 Gordon Linoff

    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;
    

  • answered 2020-02-19 11:24 Yogesh Sharma

    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;