IF statement within a formula in a SQL query

Question

Let's say I have a table with two numeric columns: NUM and DEN. I need to extract the ratio NUM/DEN only if DEN isn't 0: otherwise the ratio should be 0.

Something like this:

select ID, [...] AS RATIO
from Table

where [...] is some kind of equivalent of the excel formula IF(DEN=0;0;NUM/DEN).

Is there a way to perform this kind of query?

Many thanks!

Possible duplicate of [How to perform an IF...THEN in an SQL SELECT?](http://stackoverflow.com/questions/63447/how-to-perform-an-if-then-in-an-sql-select) — uTeisT, Nov 11 '16 at 14:08
You would have spent way less time, if you actually googled it first.. *downvoted & flagged* — uTeisT, Nov 11 '16 at 14:09
There are good answers below. If they helped you, could you please mark one as answered? You have asked other questions on SO and haven't accepted answers in the past. I'd highly encourage you to put closure to your questions by marking one as an answer. — zedfoxus, Nov 12 '16 at 00:59

score 1 · Answer 1 · answered Nov 11 '16 at 14:06

1

This should work:

case
  when DEN = 0 then 0
  else NUM/DEN
end

answered Nov 11 '16 at 14:06

Cristian Lupascu

39,078
16
100
137

James · Answer 2 · 2016-11-11T14:20:52.690

0

If SQL Server 2012 you can use : IIF

IIF ( boolean_expression, true_value, false_value )

edited Nov 11 '16 at 14:20

answered Nov 11 '16 at 14:12

James

729
6
10

score 0 · Answer 3 · answered Nov 11 '16 at 14:13

Yes, what you are looking for is case. It has two versions:

case [variable]
    when [value1] then [output1]
    when [value2] then [output2]
    when [value3] then [output3]
    ...
    else [outputdefault] end

and

 case when [Boolean(True/false) expression 1] then [output1]
      when [Boolean(True/false) expression 2] then [output2]
      when [Boolean(True/false) expression 3] then [output3]
    ...
    else [outputdefault] end

score 0 · Answer 4 · answered Nov 11 '16 at 14:22

If you are using SQL Server you could use case statement like w0lf mentioned or you could use iif statement like so:

select iif(age > 21, 'Allowed', 'Not Allowed') as status
from test;

Example:

create table test (
  fullname varchar (20),
  age int
);

insert into test values
('John', 10),
('Matt', 90),
('Jane', 25),
('Ruby', 80),
('Randy', null);

Result

| fullname |      status |
|----------|-------------|
|     John | Not Allowed |
|     Matt |     Allowed |
|     Jane |     Allowed |
|     Ruby |     Allowed |
|    Randy | Not Allowed |

The same thing can be written as

select case when age > 21 then 'Allowed' else 'Not Allowed' end as status
from test;

case statement is used by many database engines.

If you are dealing with null and not null values, you could also use coalesce like so:

select fullname, coalesce(age, 999) as status
from test;

The result will be:

| fullname | status |
|----------|--------|
|     John |     10 |
|     Matt |     90 |
|     Jane |     25 |
|     Ruby |     80 |
|    Randy |    999 |

At first you may think that coalesce does if age is null then 999 else age. It does that, sort-of, but in particular coalesce outputs the first non-null value in a list. So, coalesce(null, null, 45) will result in 45. coalesce(null, 33, 45) will result in 33.

Feel free to play around with SQL Fiddle: http://sqlfiddle.com/#!6/a41a7/6

IF statement within a formula in a SQL query

4 Answers4