How to Use Condition in Count in SQL

1. Introduction

The COUNT() function is one of the most commonly used aggregate functions in SQL. It allows us to count the number of rows in a result set, and counting rows based on specific conditions is often needed.

In this tutorial, we’ll look at different methods for counting the number of rows in SQL, including how to perform conditional counting.

2. Problem Statement

We typically use the COUNT() function to count the number of rows in a result set. Often, we include a WHERE condition in the query to filter rows before performing the count. When we need to count the same column differently, we usually write multiple queries with different WHERE conditions. However, running multiple queries is inefficient. Instead, we can use conditional COUNT() queries to achieve aggregated counts more efficiently.

We’ll explore various ways to perform this task across multiple database systems. The queries are tested on PostgreSQL 16, MySQL 8, and SQL Server 2022. However, the queries should also work similarly in most other versions of these databases.

3. Model

We’ll use the database schema defined here to write the queries, specifically focusing on the Faculty table. This table lists university faculty members and their positions, such as Professor, Associate Professor, Assistant Professor, and Teaching Assistant.

In this guide, we’ll write queries to find the number of Professors and Assistant Professors in the university using the COUNT() function while excluding the count of other faculty members.

4. Conditional Count Query – ANSI Based

ANSI SQL defines the standards for SQL queries compatible with most databases. In this section, we’ll explore how to write ANSI-compliant queries to obtain the counts.

4.1. Using COUNT()

To determine the count of Professors and Assistant Professors from the Faculty table, the straightforward approach involves utilizing COUNT() queries with a WHERE condition for each case:

SELECT COUNT(*) FROM Faculty 
WHERE position = 'Professor';
SELECT COUNT(*) FROM Faculty 
WHERE position = 'Assistant Professor';

However, we’re required to either write multiple queries for each position needed or use sub-queries, neither of which is particularly performant:

SELECT 
    (SELECT COUNT(*) FROM Faculty WHERE position = 'Professor') AS professor_count,
    (SELECT COUNT(*) FROM Faculty WHERE position = 'Assistant Professor') AS assistant_professor_count;

Alternatively, we can use a GROUP BY clause to combine this into a single query and get the counts:

SELECT position, COUNT(*) FROM Faculty 
WHERE position IN ('Assistant Professor', 'Professor') 
GROUP BY position;

When we execute this query, we get the result as:

In this case, we added the WHERE condition to filter only the necessary positions before applying the GROUP BY clause.

4.2. Using Conditional COUNT()

The COUNT() function enables the use of conditions within the function to filter results during aggregation. Let’s explore how to obtain the counts of professors and assistant professors using a conditional count query:

SELECT 
    COUNT(CASE WHEN position = 'Assistant Professor' THEN 1 END) AS assistant_professors,
    COUNT(CASE WHEN position = 'Professor' THEN 1 END) AS professors
FROM Faculty;

We utilized conditions within the COUNT() function in this query using the CASE WHEN statements. Executing this query yields the following result:

Unlike the GROUP BY query, which returns the results in multiple rows, this query returns the results in a single row with the positions as the columns.

While GROUP BY counting is simpler in straightforward conditions, the conditional count query facilitates writing complex counts with multiple conditions spanning multiple fields. For example, we can modify the above query to get the count of professors and count of assistant professors who joined before a particular date:

SELECT 
    COUNT(CASE WHEN position = 'Assistant Professor' AND start_date < '2017-01-01' THEN 1 END) AS assistant_professors,
    COUNT(CASE WHEN position = 'Professor' THEN 1 END) AS professors
FROM Faculty;

In this query, we introduced an additional filter to include only the assistant professors who started before 2017-01-01. Even though we can achieve this using GROUP BY, the complexity of the query increases and becomes more difficult to understand.

5. Database Specific Queries

In addition to ANSI-based queries, each database management system sometimes offers unique ways to perform operations that are specific to that database. In this section, we’ll look at database-specific queries to achieve the same requirement.

5.1. Using FILTER in PostgreSQL

PostgreSQL provides an alternative method to perform the same operation using the FILTER clause alongside the COUNT() function. Let’s rewrite the query to find the count of professors and assistant professors using the FILTER approach:

SELECT 
    COUNT(*) FILTER (WHERE position = 'Assistant Professor') AS assistant_professors,
    COUNT(*) FILTER (WHERE position = 'Professor') AS professors
FROM Faculty;

Here, we defined the FILTER clause alongside the WHERE condition within the COUNT() function. It is important to wrap the WHERE condition in a parenthesis; otherwise, it results in an invalid query.

This approach enhances query readability compared to a CASE WHEN statement since we can utilize the familiar WHERE clause.

5.2. Using COUNT() With Boolean Condition in MySQL

In MySQL, we can use the IF condition in the COUNT() function to achieve the same requirement:

SELECT 
    COUNT(IF(position = 'Assistant Professor', 1, NULL)) AS assistant_professors,
    COUNT(IF(position = 'Professor', 1, NULL)) AS professors
FROM Faculty;

In this case, we use the IF condition, returning 1 if it matches, else NULL. We can further simplify this query by using SUM() instead of COUNT():

SELECT 
    SUM(position = 'Assistant Professor') AS assistant_professors,
    SUM(position = 'Professor') AS professors
FROM Faculty;

This is similar to using COUNT() with an IF condition. Within the SUM(), the condition evaluates to 1 or NULL depending on the value present, ultimately determining the count.

5.3. Using COUNT() With IIF in SQL Server

We can utilize IIF alongside COUNT() to achieve the same operation with SQL Server. The query closely resembles the MySQL example we discussed earlier. Instead of IF in MySQL, we employ IIF in SQL Server:

SELECT 
    COUNT(IIF(position = 'Assistant Professor', 1, NULL)) AS assistant_professors,
    COUNT(IIF(position = 'Professor', 1, NULL)) AS professors
FROM Faculty;

This retrieves the counts of professors and assistant professors.

6. Conclusion

In this article, we explored different ways of counting the rows based on specific conditions. We discussed the ANSI SQL-based queries that are supported across different databases. Additionally, we looked at database-specific queries in PostgreSQL, MySQL, and SQL Server.