valenzio valenzio - 23 days ago 7
SQL Question

SQL, encapsulated Group by or conditional aggregation

I have the following table in a Vertica DB:

+---------+-------+
| Readout | Event |
+---------+-------+
| 1 | A |
| 1 | B |
| 1 | A |
| 2 | B |
| 2 | A |
+---------+-------+


I would like to group each readout and count the frequency of the events, resulting in a table like this:

+---------+----------------+----------------+-----------------+
| Readout | Count(Readout) | Count(Event A) | Count (Event B) |
+---------+----------------+----------------+-----------------+
| 1 | 3 | 2 | 1 |
| 2 | 2 | 1 | 1 |
+---------+----------------+----------------+-----------------+


I am sure there is an easy
GROUP BY
command, but I can't wrap my head around it.
Thank you!

Answer

You want conditional aggregation:

select readout, count(*),
       sum(case when event = 'A' then 1 else 0 end) as num_a,
       sum(case when event = 'B' then 1 else 0 end) as num_b
from t
group by readout;