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

If does not work INSIDE a query. You can use CASE WHEN <statement> THEN <true> ELSE <false> END instead. — Jens
– Jens, Commented Sep 26, 2016 at 11:59

rbr94 · Accepted Answer · 2016-09-26 11:45:07Z

2

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.

edited Sep 26, 2016 at 11:45

answered Sep 26, 2016 at 11:27

rbr94

2,2874 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pரதீப் · Accepted Answer · 2016-09-26 11:50:19Z

1

Use CASE statement

SELECT x.id,
       x.name,
       x.type,
       CASE
         WHEN x.type = 1 THEN (SELECT Sum(Col1)
                               FROM   TableA Where...)
         WHEN x.type = 2 THEN (SELECT Sum(Col2)
                               FROM   TableB  Where .. )
       END AS Total
FROM   TableX AS x

edited Sep 26, 2016 at 11:50

answered Sep 26, 2016 at 11:34

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Comments

neer · Accepted Answer · 2016-09-26 11:33:56Z

1

You can use CASE WHEN as the below:

SELECT 
    x.id, 
    x.name, 
    x.type,
    CASE 
        WHEN x.type = 1 THEN (SELECT SUM(A.Col1) FROM TableA A WHERE 1 = 1)
        WHEN x.type = 2 THEN (SELECT SUM(B.Col2) FROM TableB B WHERE 1 = 1)
        ELSE NULL END AS Total
FROM 
    TableX as x

answered Sep 26, 2016 at 11:33

neer

4,0926 gold badges22 silver badges34 bronze badges

Comments

sagi · Accepted Answer · 2016-09-26 11:35:11Z

1

You can use case expression:

select t.* ,
            Case when t.type = 1 then (select sum(col1) ... TableA)
                      when t.type = 2 then (select sum(col2) ... TableB)
             End as Total
From tableX t

answered Sep 26, 2016 at 11:35

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Comments

Pintu Kawar · Accepted Answer · 2016-09-26 12:24:10Z

0

By using variable -

DECLARE @SumA INT = SELECT SUM(Col1) FROM TableA WHERE ... etc
DECLARE @SumB INT = SELECT SUM(Col2) FROM TableB WHERE ... etc

SELECT x.id
   , x.name
   , x.type
   ,( CASE x.type
        WHEN 1 THEN @SumA
        WHEN 2 THEN @SumB
      END
    ) AS Total
FROM TableX as x

Choose the datatype for Sum variable accordingly (if Decimal).

answered Sep 26, 2016 at 12:24

Pintu Kawar

2,1564 gold badges25 silver badges39 bronze badges

2 Comments

wingyip Over a year ago

Would the variable queries be run before the main query is run or only when they are called?

Pintu Kawar Over a year ago

It has to be run in a single batch.

Collectives™ on Stack Overflow

Use IF in SELECT part of query

5 Answers 5

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related