Geek Answers Handbook

Use IF in the SELECT part of a query

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 another sub request according to the value of x.type

wings

+5
source share
5 answers

Use the 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
+1
source

Try using LEFT JOIN and COALESCE . Use x.type to join tables.

COALESCE (Transact-SQL): evaluates the arguments in order and returns the current value of the first expression, which is not initially 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 determine which column to use.

+2
source

You can use CASE WHEN as shown 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
+1
source

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
+1
source

Using the 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

Select the data type for the Sum variable accordingly (if Decimal).

0
source

More articles:


All Articles