mysql if function

Syntax:

IF(expr,if_true_expr,if_false_expr)

  • MySQL IF function is control flow functions that returns a value based on a condition.
  • The IF function is sometimes referred to as IF ELSE or IF THEN ELSE function.
  • If the expr evaluates to TRUE i.e., expr is not NULL and expr is not 0, the IF function returns the if_true_expr , otherwise, it returns if_false_expr The IF function returns a numeric or a string, depending on how it is used.

 

Let’s have an simple example:

 

We have a table with null value in state column, Now using IF function we can replace null with N/A values.

SELECT  customerNumber, customerName, IF(state IS NULL, ‘N/A’, state) state, country FROM customers;

Example used with count function:

We have orders table in which we need to find the status of the object.

In Raw table we have the status column that contains status of the object as dispatched, hold, in progress, cancel etc.

order status

order status

Now Using if function we can easily show the data in the reports as follows:

mysql if
mysql if

SELECT  COUNT(IF(status = ‘Cancel’, 1, NULL)) Cancel, COUNT(IF(status = ‘Dispatched’, 1, NULL)) Dispatched, COUNT(IF(status = ‘In Process’, 1, NULL)) ‘In Process’, COUNT(IF(status = ‘Hold’, 1, NULL)) ‘Hold’ FROM orders;

Example used with sum function:

We can also use the if function to check the status summary of orders.

SELECT SUM(IF(status = ‘Hold’, 1, 0)) AS Hold, SUM(IF(status = ‘Cancel’, 1, 0)) AS Cancel FROM orders;

 

Related Posts

  • 65
    MySQL TRIM() function returns a string after removing all prefixes or suffixes from the given string. Syntax TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM ] str) Arguments Name Description BOTH Indicates that prefixes from both left and right are to be removed. LEADING Indicates that only leading prefixes are to…
    Tags: mysql, function, select, example, returns
  • 64
    MySQL has a RAND function that can be invoked to produce random numbers between 0 and 1: mysql> SELECT RAND( ), RAND( ), RAND( ); +------------------+-----------------+------------------+ | 0.45464584925645 | 0.1824410643265 | 0.54826780459682 | +------------------+-----------------+------------------+ 1 row in set (0.00 sec) When invoked with an integer argument, RAND( ) uses that…
    Tags: mysql, select, function
  • 63
    MySQL LOCATE() returns the position of the first occurrence of a string within a string. Both of these strings are passed as arguments. An optional argument may be used to specify from which position of the string (i.e. string to be searched) searching will start. If this position is not…
    Tags: mysql, select, returns, function
  • 59
    Description: MySQL LENGTH() returns the length of a given string. Syntax: LENGTH (str) Argument: Name      Description str            A string whose length is to be returned. Example of MySQL LENGTH() function mysql> SELECT LENGTH(NULL); Result: NULL mysql> SELECT LENGTH(''); Result: 0 mysql> SELECT LENGTH('…
    Tags: mysql, select, function
  • 56
    Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called. For more about stored procedure and function refer the articles Different types of…
    Tags: function, select

Satya Prakash

VOIP Expert: More than 8 years of experience in Asterisk Development and Call Center operation Management. Unique Combination of Skill Set as IT, Analytics and operation management.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: