TimeScaleDb/Postgres: Materialized Views(COGG): GROUP BY: group by certain field values

What I'm currently doing is this:

SELECT
    time_bucket('60 min', raw_data.timestamp) AS time_60min,
    COUNT(raw_data.vehicle_class) AS "count",
    raw_data.vehicle_class AS "vehicle_class"
FROM bma_raw_data_ode AS raw_data
WHERE raw_data.vehicle_class IN ('car', 'bus', 'van', 'motorbike')
GROUP BY time_60min, raw_data.vehicle_class
ORDER BY time_60min

All entries in raw_data.vehicle_class have one of 12 given values (car, bus, person, bicycle and so on) - think of it as an enum, as it can only be one of these 12 values.

What the query above returns at the moment is the count of each vehicle_class in raw_data within an hour:

"2025-06-10 19:00:00+00"    1   "bus"
"2025-06-10 19:00:00+00"    4   "motorbike"
"2025-06-10 19:00:00+00"    126 "car"
"2025-06-10 19:00:00+00"    3   "van"

Now, what I'd like to achieve is: Not having separate car and van rows per hour, but a single row for the sum of both counts.

So, the result I'm looking for would be someting like:

"2025-06-10 19:00:00+00"    1   "bus"
"2025-06-10 19:00:00+00"    4   "motorbike"
"2025-06-10 19:00:00+00"    129 "car + van"

Can you point be in the right direction? Can I achieve this directly in COUNT(raw_data.vehicle_class) AS "count",, can it be done by a SELECT in the GROUP BY statement or is some fancy join on a virtual table the right way?

Thanks

You will need to create a new column (here named new_vehicle_class) containing the desired mapping and group by this column.

To do so you can use the following query:

SELECT SUB_QUERY.time_60min, COUNT(SUB_QUERY.new_vehicle_class) AS "count", SUB_QUERY.new_vehicle_class  
FROM (
    SELECT
        time_bucket('60 min', raw_data.timestamp) AS time_60min,
        CASE 
            WHEN raw_data.vehicle_class = 'car' OR raw_data.vehicle_class = 'van' THEN 'car + van' 
            ELSE raw_data.vehicle_class 
        END AS new_vehicle_class
    FROM bma_raw_data_ode AS raw_data
    WHERE raw_data.vehicle_class IN ('car', 'bus', 'van', 'motorbike')
) SUB_QUERY
GROUP BY SUB_QUERY.time_60min, SUB_QUERY.new_vehicle_class
ORDER BY SUB_QUERY.time_60min

If you have more then 1 new class (here 'car + van') that you need to map together you can add a WHEN ... THEN in the CASE. You can also map more than 2 value to 1 using multiples OR's (more than 1 OR). Here is a reference on how to use CASE: https://www.w3schools.com/sql/sql_case.asp