How to Use Count With Condition in PostgreSQL?
Last Updated :
12 Feb, 2024
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 CountedExplanation:
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 CountedExplanation: 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.
Similar Reads
SQL Interview Questions Are you preparing for a SQL interview? SQL is a standard database language used for accessing and manipulating data in databases. It stands for Structured Query Language and was developed by IBM in the 1970's, SQL allows us to create, read, update, and delete data with simple yet effective commands.
15+ min read
SQL Tutorial SQL is a Structured query language used to access and manipulate data in databases. SQL stands for Structured Query Language. We can create, update, delete, and retrieve data in databases like MySQL, Oracle, PostgreSQL, etc. Overall, SQL is a query language that communicates with databases.In this S
11 min read
Non-linear Components In electrical circuits, Non-linear Components are electronic devices that need an external power source to operate actively. Non-Linear Components are those that are changed with respect to the voltage and current. Elements that do not follow ohm's law are called Non-linear Components. Non-linear Co
11 min read
SQL Commands | DDL, DQL, DML, DCL and TCL Commands SQL commands are crucial for managing databases effectively. These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). In this article, we will e
7 min read
SQL Joins (Inner, Left, Right and Full Join) SQL joins are fundamental tools for combining data from multiple tables in relational databases. Joins allow efficient data retrieval, which is essential for generating meaningful observations and solving complex business queries. Understanding SQL join types, such as INNER JOIN, LEFT JOIN, RIGHT JO
6 min read
Normal Forms in DBMS In the world of database management, Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and maintaining data integrity. When working with databases, especially relational databases, it is critical to follow normalization techniques that help to eliminate
7 min read
Spring Boot Tutorial Spring Boot is a Java framework that makes it easier to create and run Java applications. It simplifies the configuration and setup process, allowing developers to focus more on writing code for their applications. This Spring Boot Tutorial is a comprehensive guide that covers both basic and advance
10 min read
ACID Properties in DBMS In the world of DBMS, transactions are fundamental operations that allow us to modify and retrieve data. However, to ensure the integrity of a database, it is important that these transactions are executed in a way that maintains consistency, correctness, and reliability. This is where the ACID prop
8 min read
Class Diagram | Unified Modeling Language (UML) A UML class diagram is a visual tool that represents the structure of a system by showing its classes, attributes, methods, and the relationships between them. It helps everyone involved in a projectâlike developers and designersâunderstand how the system is organized and how its components interact
12 min read
Steady State Response In this article, we are going to discuss the steady-state response. We will see what is steady state response in Time domain analysis. We will then discuss some of the standard test signals used in finding the response of a response. We also discuss the first-order response for different signals. We
9 min read