Search code examples
mysqlsqlcalculated-columns

How to create a conditional calculated column in SQL?

How to write a query to create a conditional and calculated field in SQL?

I am trying to merge two or more entities and the context of the entities are similar to the sample ones below. I am having an issue with regards to how to create the calculated field with the column F and £ of C-A. I would greatly appreciate if I could get some help. 

FlightTable                  AirportTable

AKEY  CODE  F    £           Code  City      Country
001   LHR   C    10          ATL   Atlanta   USA
002   BOS   C    15          BOS   Boston    USA
003   BOS   A    9            . . . . . . . . . . . . .
 . . . . . . . . .            . . . . . . . . . . . . . 
101   MAN   C    21          VIE   Schwechat Austria 
102   VIE   A    9           ZRH   Kloten    Switzerland

I am trying to get a result of a joined entity similar to: 

Joined table

     CODE CITY    COUNTRY (£ Calculated Field)
001  BOS  Boston  USA      4
 . . . . . . . . . . . . . .
Solution

  • You may be able to use a case statement in the calculation. 

         SELECT AIR.Code, AIR.City, AIR.Country, SUM(CASE WHEN flight.f = 'C' then £
                                                        when flight.f = 'A' then (-1) * £
                                                        else null
                                                        end) as "£ calculated"
     from AirportTable as "air" 
     join FlightTable as "flight" on "air".CODE = "flight".code -- could be AKEY = AKEY