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)Copy

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))Copy

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 COUNTIF.

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
);Copy

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');Copy

Third, retrieve data from the employees table:

SELECT 
  full_name, 
  employee_type 
FROM 
  employees;Copy

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)Copy

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;Copy

Output:

full_time_employee_count
------------------------
5Copy

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;Copy

Output:

full_time | contractor | temp
----------+------------+------
5         | 5          | 1
(1 row)Copy

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;Copy

Output:

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

(3 rows affected)Copy

Summary #

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

Was this tutorial helpful?

YesNo