{"id":10840,"date":"2023-10-08T23:24:01","date_gmt":"2023-10-09T06:24:01","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=10840"},"modified":"2023-10-08T23:24:02","modified_gmt":"2023-10-09T06:24:02","slug":"mysql-count-if","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-aggregate-functions\/mysql-count-if\/","title":{"rendered":"MySQL COUNT IF"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL COUNT<\/code> IF<\/code> to count values in a set based on a condition.<\/p>\n\n\n\n

Introduction to MySQL COUNT IF function<\/h2>\n\n\n\n

The IF<\/code> function<\/a> evaluates an expression and returns a value depending on whether the result of the expression is true or false.<\/p>\n\n\n\n

Here’s the syntax of the IF<\/code> function:<\/p>\n\n\n

IF(expression, value_if_true, value_if_false)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the IF<\/code> function returns value_if_true<\/code> if the expression is true or value_if_false<\/code> if the expression is false.<\/p>\n\n\n\n
COUNT(IF(condition,1, NULL));<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the COUNT()<\/code> will return the number of values that make the condition true. Note that you can use another value other than the number 1.<\/p>\n\n\n\n
MySQL COUNT IF example<\/h2>\n\n\n\n
First, create a new table<\/a> called orders<\/code> with four columns order_id<\/code>, customer_name<\/code>, order_date<\/code>, and order_status<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> orders (\n    order_id INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    customer_name VARCHAR<\/span>(50<\/span>),\n    order_date DATE<\/span>,\n    order_status VARCHAR<\/span>(20<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> orders (customer_name, order_date, order_status)\nVALUES<\/span>\n    ('Alice'<\/span>, '2023-01-15'<\/span>, 'Shipped'<\/span>),\n    ('Bob'<\/span>, '2023-02-20'<\/span>, 'Pending'<\/span>),\n    ('Charlie'<\/span>, '2023-03-10'<\/span>, 'Shipped'<\/span>),\n    ('David'<\/span>, '2023-04-05'<\/span>, 'Delivered'<\/span>),\n    ('Eve'<\/span>, '2023-05-12'<\/span>, 'Shipped'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third,  query data from the orders<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> orders;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----------+---------------+------------+--------------+\r\n| order_id | customer_name | order_date | order_status |\r\n+----------+---------------+------------+--------------+\r\n|        1<\/span> | Alice         | 2023<\/span>-01<\/span>-15<\/span> | Shipped      |\r\n|        2<\/span> | Bob           | 2023<\/span>-02<\/span>-20<\/span> | Pending      |\r\n|        3<\/span> | Charlie       | 2023<\/span>-03<\/span>-10<\/span> | Shipped      |\r\n|        4<\/span> | David         | 2023<\/span>-04<\/span>-05<\/span> | Delivered    |\r\n|        5<\/span> | Eve           | 2023<\/span>-05<\/span>-12<\/span> | Shipped      |\r\n+----------+---------------+------------+--------------+\r\n5<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, count the orders by status using the COUNT<\/code> IF<\/code> to count the number of shipped orders:<\/p>\n\n\n
SELECT<\/span> \n  COUNT<\/span>(\n    IF<\/span>(order_status = 'Shipped'<\/span>, 1<\/span>, NULL<\/span>)\n  ) AS<\/span> shipped_orders_count \nFROM<\/span> \n  orders;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----------------------+<\/span>\n| shipped_orders_count |\n+----------------------+<\/span>\n|                    3 |\n+----------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
There are three shipped orders.  <\/p>\n\n\n\n
In this example:<\/p>\n\n\n\n
    \n
  • The IF(order_status = 'Shipped', 1, NULL)<\/code> returns 1 if the order_status<\/code> is shipped<\/code> or NULL<\/code> otherwise.<\/li>\n\n\n\n
  • The COUNT<\/code> will count only 1 and ignore the NULL<\/code> value. Therefore, it returns the number of shipped orders.<\/li>\n<\/ul>\n\n\n\n
    Similarly, you can use the COUNT<\/code> IF<\/code> to counter the number of orders by status:<\/p>\n\n\n
    SELECT<\/span> \n  COUNT<\/span>( IF<\/span>(order_status = 'Shipped'<\/span>, 1<\/span>, NULL<\/span>)) AS<\/span> shipped_orders_count, \n  COUNT<\/span>( IF<\/span>(order_status = 'Pending'<\/span>, 1<\/span>, NULL<\/span>)) AS<\/span> pending_orders_count, \n  COUNT<\/span>( IF<\/span>(order_status = 'Delivered'<\/span>, 1<\/span>, NULL<\/span>)) AS<\/span> delivered_orders_count \nFROM<\/span> \n  orders;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +----------------------+----------------------+------------------------+<\/span>\n| shipped_orders_count | pending_orders_count | delivered_orders_count |\n+----------------------+----------------------+------------------------+<\/span>\n|                    3 |                    1 |                      1 |\n+----------------------+----------------------+------------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use MySQL COUNT<\/code> IF<\/code> to count a number of values based on a specific condition.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
Second, insert some rows<\/a> into the orders<\/code> table with different order statuses:<\/p>\n\n\n
The COUNT()<\/a><\/code> function is an aggregate function<\/a> that returns the number of non-null values in a set. Because the COUNT()<\/code> function accepts an expression, you can use the IF<\/code> to form an expression like this:<\/p>\n\n\n