How to Use Count With Condition in PostgreSQL?

In PostgreSQL, the COUNT() function serves as a tool for tallying the number of records within a table. This article aims to address this query, delving into the nuances and implications of integrating conditions into the COUNT() function in PostgreSQL. The COUNT() function in PostgreSQL is traditionally employed to count all records within a dataset. However, there are instances where counting records based on specific conditions becomes necessary. Fortunately, PostgreSQL facilitates the use of conditions within the COUNT() function, enabling a more intricate approach to record counting.

Count() in PostgreSQL

The COUNT() aggregate function plays a pivotal role in PostgreSQL queries, allowing for the enumeration of rows within a table. This function serves as a robust tool for data analysis and aggregation. By default, COUNT(*) retrieves the total number of rows in the table, yet its versatility shines when conditions are applied using the WHERE clause or CASE expressions, enabling more precise counting based on specific criteria.

Counting Using Where Clause with Count() Function

Using the COUNT() function with the WHERE clause you will get the count of those rows which justify the specific condition.

Syntax:

SELECT COUNT(*)

FROM table_name

WHERE condition;

The COUNT() function can be used alongside the WHERE clause to count rows selectively based on specified conditions. This allows us to include or exclude rows from the count based on criteria we define.

Example of Counting Rows Using COUNT() Function

Consider a table named ‘Students_1’ where you have to count the number of student who have Marks greater than 60. To count the number of students you will use COUNT(*) function and to apply the condition, where only those students who have marks greater than 60 will be counted. You will need to use the WHERE clause.

SELECT COUNT(*)
FROM Students_1
WHERE Marks > 60;

Output:

17 Rows Counted

Explanation:

The above output shows the number of students whose marks are equal or greater than 60. So, you can see that there are 17 students whose marks are above 60.

Counting Using CASE with Count() Function

The CASE expression is used to give condition and if that condition is true it will return the output specifically 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;

In the CASE expression – WHEN, if the condition evaluates to true, the corresponding THEN result is returned. If no conditions match, the ELSE result is returned.

Example of Counting Rows Using COUNT() Function With CASE

Suppose you have a ‘Student_1’ table and you have to count number of students and their marks. Using COUNT() function with CASE, you can tally count based on a category field like ‘Student’, ‘Marks’ etc.

SELECT
  COUNT(CASE WHEN marks >= 90 THEN 1 END) AS A,
  COUNT(CASE WHEN marks >= 80 AND marks < 90 THEN 1 END) AS B,
  COUNT(CASE WHEN marks >= 70 AND marks < 80 THEN 1 END) AS C,
  COUNT(CASE WHEN marks >= 60 AND marks < 70 THEN 1 END) AS D,
  COUNT(CASE WHEN marks < 60 THEN 1 END) AS E
FROM student_1;

Output:

Rows Counted

Explanation: In the above example you can see that the you can see that there are 3 student whose marks are greater than 90, 4 students whose marks are greater than or equal to 80 but less than 90, 3 students whose marls are greater than or equal to 70 but less than 80, 7 students whose marks are greater than or equal to 60 but less than 70 and 6 students whose marks are less than 60.

Conclusion

In conclusion, integrating the CASE keyword and the WHERE clause in PostgreSQL presents a powerful method for data handling and analysis. The CASE keyword facilitates dynamic result generation through conditional logic, while the WHERE clause filters data according to specific conditions. By combining these features, PostgreSQL queries are empowered to extract valuable insights and support informed decision-making, enhancing analytical precision and efficiency.