Use IF in SELECT part of query

Question

I need to create something like this

SELECT x.id
   , x.name
   , x.type
   ,(
      IF x.type = 1
         (SELECT SUM(Col1) FROM TableA WHERE ... etc)
      ELSE IF x.type = 2
         (SELECT SUM(Col2) FROM TableB WHERE ... etc)
    ) AS Total
FROM TableX as x

So I am trying to select a different sub query according to the value of x.type

Wing

Answer 1

Try to use LEFT JOIN and COALESCE. Use your conditions of x.type to join the tables.

COALESCE (Transact-SQL): Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL. https://msdn.microsoft.com/en-us/library/ms190349.aspx

SELECT x.id
   , x.name
   , x.type
   , COALESCE(SUM(TableA.Column), SUM(TableB.Column)) as column_xyz
FROM TableX as x
LEFT JOIN TableA ON x.type = 1 AND ...
LEFT JOIN TableB ON x.type = 2 AND ...

You can also use CASE WHEN ... THEN ... instead of COALESCE to define which column to use.