How to Specify Condition in Count() in SQL?

In SQL, the COUNT() function is a fundamental tool for counting the number of records in a given dataset. However, in some cases, it is necessary to count the number of records based on certain conditions. Fortunately, SQL supports conditions in the COUNT() method, which allows a more complex approach to counting records.

This article explores the possibilities and implications of adding conditions to the COUNT() function in SQL.

Specify Condition in COUNT Function in SQL

There are two ways to specify conditions in the COUNT function in SQL:

Using WHERE Clause to Specify Condition in COUNT() in SQL

The WHERE clause can be used with COUNT() to specify a condition and count only the rows that meet a specified condition.

Syntax

SELECT COUNT(*) FROM table_name WHERE condition;

Here, the WHERE clause allows us to specify conditions based on which the rows are included or excluded from the count

Example

In this example, we use the Count() aggregate function which counts the number of rows in a table with the Where clause which is used to apply conditions in SQL query.

Consider a scenario where we have a table named Employee with a column named Salary. We want to count the number of employees with a salary greater than 50000.

SELECT Count(*) 
FROM employee
WHERE salary >= 50000;

Output:

Explanation:

This output shows the number of employees whose salary is equal to or greater than 50000. This returns 5 which means there are 5 employees whose salary is equal to or greater than 50000.

Using CASE Statement to Specify Condition in COUNT() in SQL

The CASE expression allows us to define conditions and return values selectively based on those conditions.

Syntax

SELECT
COUNT(CASE WHEN condition1 THEN expression1 ELSE NULL END) AS Alias1,
COUNT(CASE WHEN condition2 THEN expression2 ELSE NULL END) AS Alias2
FROM table_name
WHERE condition;

Example

In this example, we going to use the Count() aggregate function with CASE which is used to define the conditions and return values based on the condition. In this example we fetch the number of employees from different departments like IT, HR, SALES department.

SELECT
  COUNT(CASE WHEN department = 'IT' THEN 1 END) AS IT,
  COUNT(CASE WHEN department = 'HR' THEN 1 END) AS HR,
  COUNT(CASE WHEN department = 'SALES' THEN 1 END) AS SALES
FROM employees;

This will return the count of employees for each department category. The CASE expression checks the value of the Department and if it matches, returns 1, which is tallied by COUNT(). Else, it returns NULL which is ignored.

Output:

Explanation:

This output shows the number of employees in IT, HR, and SALES departments. In this output, it shows there are 2 employees from the IT department, 2 employees in the HR department and 2 employees in the SALES department.

Conclusion

In conclusion, using the CASE keyword and the WHERE clause in SQL allows to specify conditions in COUNT(). The CASE keyword enables conditional logic for dynamic result generation, while the WHERE clause filters data based on specified conditions. Together, they enhance the analytical capabilities of SQL queries, allowing users to extract meaningful insights and make informed decisions from their datasets with precision and efficiency