Sql query cte

,
1

car | price | condition

nissan|10 | old
nissan|20 | old
nissan|30 | new
BMW|40 | old
BMW|100 | new
audi|40 | old
audi|100 | old
audi|15 | old
audi|35 | old

OUTPUT Required:

car | difference
nissan| 30-10=20(max-min) if max is new condition
bmw | 100-40=60(max-min) if max is new condition
audi |15 all are old get the lowest one

2

My answer has nothing to do with cte - please explain what it is you want in regard to cte:

select car
      ,case
          when sum(case when condition='new' then 1 else 0 end)=0
          then min(price)
          when sum(case when condition='old' then 1 else 0 end)=0
          then max(price)
          else max(price)-min(price)
       end as difference
  from yourtable
 group by car
;
1 Like
3

thank you very much