The COUNT() function in PostgreSQL is an aggregate function that returns the number of rows or non-NULL values in a column. It is commonly used to count records in a table and can be combined with clauses such as WHERE, GROUP BY and HAVING. The COUNT() function helps users to:
- Count the total number of rows in a table.
- Count records that satisfy a specific condition.
- Use with GROUP BY and HAVING to summarize data.
Syntax
COUNT(expression)or
COUNT(*)Where:
- expression: The column whose non-NULL values are counted.
- *: Counts all rows in the table, including rows containing NULL values.
Examples
Firstly, create a students table and insert some records.
Example 1: Count Total Rows
The following query returns the total number of students.
Query:
SELECT COUNT(*) AS total_students
FROM students;
Output:
- The COUNT(*) function counts every row in the students table.
- It includes rows containing NULL values.
Example 2: COUNT() on a Column
The following query counts the number of students whose marks value is not NULL.
Query:
SELECT COUNT(marks) AS students_with_marks
FROM students;
Output:
- The COUNT(marks) function counts only non-NULL values in the marks column.
- The row with NULL marks is ignored.
Example 3: COUNT() with GROUP BY
The following query counts the number of students in each course.
Query:
SELECT
course,
COUNT(*) AS total_students
FROM students
GROUP BY course;
Output:
- The GROUP BY clause groups students by course.
- The COUNT(*) function returns the number of students in each course.
Example 4: COUNT() with HAVING Clause
The following query displays courses having more than one student.
Query:
SELECT
course,
COUNT(*) AS total_students
FROM students
GROUP BY course
HAVING COUNT(*) > 1;
Output:
- The GROUP BY clause groups records by course.
- The HAVING clause filters groups with more than one student.
- Only the Computer Science course satisfies the condition.