Search code examples
sqlpostgresqlcaseaggregate-functions

GROUP BY except for specific values that need to be added up

I have a table like this:

id | type
---+------
1  | plane
2  | plane
3  | ferry
4  | ferry
5  | car
6  | car
7  | bus
8  | train

We can count the rows for each type:

select type, count(*) from transport group by type;

type  | count
------+------
plane | 2
ferry | 2
car   | 2
bus   | 1
train | 1

This works, but notice how the last three types - car, bus, train - are ground transport (while plane is air and ferry is sea). What I really want is:

type   | count
-------+------
plane  | 2
ferry  | 2
car    | 4

I could sum up the counts at the application level, but is this feasible in SQL? Thank you.

Solution

  • You can use a case expression to reassign buses and trains to the car group:

    select
    case when type in ('bus', 'train') then 'car' else type end as new_type,
    count(*)
from transport
group by new_type