MySQL If Else condition in Select

IF Else condition in query is quite interesting and it is very useful in some cases. It reduces two queries to single one.

IF(Statement, TRUE block, FALSE block)
It works like usual If Else condition which checks the Statement if it is True then goto TRUE Block else FALSE Block.

Example 1
Id name growth value($)
101 Ravi up 250
102 Roy down 747
103 Joe down 5383
104 Mark up 34
Select id, name, IF(growth = 'up', value, value*-1) as profit_or_loss from report;
Id name profit_or_loss
101 Ravi 250
102 Roy -747
103 Joe -5383
104 Mark 34

In this example query, condition checks growth is 'up', if 'up' it returns the actual value else it will negative value of it by multiplying -1.
Example 2
Id name status
221 Ravi 0
222 Roy 0
223 Ravi 1
224 Ravi 1
225 Joe 1
SELECT id, name, count(*) as Total, SUM(IF(status=1,1,0)) AS Reviewed, SUM(IF(status=0,1,0)) AS Not reviewed FROM points group by id

This is an another useful example to get the total Reviewed and Not reviewed count of each users from the table in a Single query.
Id name Total Reviewed Not reviewed
221 Ravi 3 2 1
222 Roy 1 0 1
223 Joe 1 1 0
MySQL SQL Posted on 2016-04-09 03:52:47