Q:

Encapsulated Group by or conditional aggregation in Vertica DB

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.

A:

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;

