w3resource logo


postgresql count functions

PostgreSQL COUNT function

rating PostgreSQL COUNT function has average rating 8 out of 10. Total 12 users rated.

<<PreviousNext>>

Description

The PostgreSQL COUNT function counts number of rows or non-NULL values against a specific column from a table. When an asterisk(*) is used with count function the total number of rows returns.

Syntax

COUNT (* | [DISTINCT] ALL | column_name)

Parameters

Name Description
column_name Name of the column
* The asterisk(*) indicates all the rows.
DISTINCT This clause is optional. It indicates uniqueness.
ALL This clause is optional. It is default clause.

PostgreSQL COUNT function example1

The sample table

postgresql sample table employee example1

If we want to get the number or rows in employee table, the following SQL can be used.

SQL


SELECT COUNT(*) 

FROM employee;

              

Output

postgresql count function example1

PostgreSQL COUNT on specific column

If we want to get the number of employee who earn commission in employee table, the following SQL can be used.

SQL


SELECT COUNT(commission) 

FROM employee;

              

Output

postgresql count function example2

Explain

The above example shows that, only 3 employees earn commission because, the COUNT function ignored the NULL values.

PostgreSQL COUNT DISTINCT

If we want to get the number of designation available in employee table, the following SQL can be used.

SQL


SELECT COUNT ( DISTINCT designame)

FROM employee;

              

Output

postgresql count function example3

Explain

The above example shows that, only 6 rows have returns from the employee table because the DISTINCT clause have used. The DISTINCT clause eliminates the repetition of each designame and returns only once.



<<PreviousNext>>