How to select only the value of the column phase that corresponds to the value of the column type?, that way I want to show only the TAXI_SMALL values if the value of type is small_airport, or TAXI_LARGE If the value of type is large_airport as in a sample of my table:
ident | type | aircraft | phase | co2_lto_kg |
---|---|---|---|---|
LFPG | large_airport | AC300 | APPROACH | 2033.64 |
LFPG | large_airport | AC300 | CLIMB OUT | 3279.83 |
LFPG | large_airport | AC300 | TAKE OFF | 1281.1932 |
LFPG | large_airport | AC300 | TAXI_LARGE | 3675.672 |
LFPG | large_airport | AC300 | TAXI_MEDIUM | 2261.952 |
LFPG | large_airport | AC300 | TAXI_SMALL | 1413.72 |
The desired result:
ident | type | aircraft | phase | co2_lto_kg |
---|---|---|---|---|
LFPG | large_airport | AC300 | APPROACH | 2033.64 |
LFPG | large_airport | AC300 | CLIMB OUT | 3279.83 |
LFPG | large_airport | AC300 | TAKE OFF | 1281.1932 |
LFPG | large_airport | AC300 | TAXI_LARGE | 3675.672 |
This is the code I've so far and I know that the CASE
instance is not at all right...
select
ident, type, aircraft, phase,
avg(co2_by_phase_lto) as co2_lto_kg,
case(
WHEN t.type ilike 'large'||'%' THEN phase('TAXI_LARGE')
)
from aviation.emissionsco2 as t
where t.aircraft ilike '%'||'300' and
t.rep_airp ilike '%'||'lfpg'
group by ident,phase