SQL Server COUNT IF

Summary: in this tutorial, you will learn how to use the SQL Server COUNT IF function to count values in a set based on conditions.

How to form a SQL Server COUNT IF

In SQL Server, the IIF function allows you to evaluate an expression and returns a value if the expression is true or another value if the expression is false.

Here’s the syntax of the IFF function:

IIF(expression, value_if_true, value_if_false)Code language: SQL (Structured Query Language) (sql)

The COUNT function is an aggregate function that allows you to calculate the total number of values in a set. The COUNT function ignores NULL when counting.

To count values based on a specific condition, you can combine the COUNT with the IIF function:

COUNT(IIF(expression, 1 , NULL))Code language: SQL (Structured Query Language) (sql)

In this expression, the COUNT will calculate the total number of values when the expression is true. If the expression is false, the IIF function returns NULL. Hence, the COUNT function will not count it.

SQL Server COUNT IF examples

Let’s explore some examples of using the SQL Server COUNT IF.

1) Setting up a sample table

First, create a table called employees that stores the employee data:

CREATE TABLE employees(
  id INT IDENTITY PRIMARY KEY, 
  full_name VARCHAR(255) NOT NULL, 
  employee_type varchar(25) not null
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the employees table:

INSERT INTO employees (full_name, employee_type) 
VALUES 
  ('John Doe', 'Full-time'), 
  ('Jane Smith', 'Full-time'), 
  ('Michael Johnson', 'Full-time'), 
  ('Emily Brown', 'Full-time'), 
  ('David Lee', 'Contractor'), 
  ('Sarah Williams', 'Temp'), 
  ('Matthew Taylor', 'Full-time'), 
  ('Jessica Martinez', 'Contractor');Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the employees table:

SELECT 
  full_name, 
  employee_type 
FROM 
  employees;Code language: SQL (Structured Query Language) (sql)

Output:

full_name        | employee_type
------------------------------
John Doe         | Full-time
Jane Smith       | Full-time
Michael Johnson  | Full-time
Emily Brown      | Full-time
David Lee        | Contractor
Sarah Williams   | Temp
Matthew Taylor   | Full-time
Jessica Martinez | Contractor
(8 rows)Code language: SQL (Structured Query Language) (sql)

2) SQL Server COUNT IF example

The following statement uses the COUNT with the IIF function to get the total number of full-time employees:

SELECT COUNT(IIF(employee_type='Full-time',1,NULL)) full_time_employee_count
FROM employees;Code language: SQL (Structured Query Language) (sql)

Output:

full_time_employee_count
------------------------
5Code language: SQL (Structured Query Language) (sql)

The following example uses the COUNT with the IIF function to get the total number of each employee type:

SELECT 
   COUNT(IIF(employee_type='Full-time',1,NULL)) full_time,
   COUNT(IIF(employee_type='Contractor',1,NULL)) contractor,
   COUNT(IIF(employee_type='Temp',1,NULL)) temp
FROM 
   employees;Code language: SQL (Structured Query Language) (sql)

Output:

full_time | contractor | temp
----------+------------+------
5         | 5          | 1
(1 row)Code language: SQL (Structured Query Language) (sql)

Note that if you use the GROUP BY clause, you’ll get the rows arranged vertically:

SELECT 
  employee_type, 
  COUNT(*) employee_count 
FROM 
  employees 
GROUP BY 
  employee_type;Code language: SQL (Structured Query Language) (sql)

Output:

employee_type             employee_count
------------------------- --------------
Contractor                2
Full-time                 5
Temp                      1

(3 rows affected)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the COUNT with the IIF function to obtain the total number of rows based on a condition.