0

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
0

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'));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.