PostgreSQL Select value row depending on another value row

postgresql

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

Best Answer

You can use NOT LIKE to include all the non taxi related data -- check that the phase doesn't begin with TAXI_. Then add for each size a comparison that couples the airport size to the taxi route with OR.

SELECT *
       FROM aviation.emissionsco2
       WHERE phase NOT LIKE 'TAXI$_%' ESCAPE '$'
              OR (phase,
                  type) IN (('TAXI_LARGE',
                             'large_airport'),
                            ('TAXI_MEDIUM',
                             'medium_airport'),
                            ('TAXI_SMALL',
                             'small_airport'));
Related Question