PostgreSQL – COUNT

COUNT function returns the count of number of rows returned by a sql query.

Syntax to return the number of rows in a table:

SELECT COUNT(*) from table_name;

Consider the below employee table:


Below query returns the number of rows in the employee table:

SELECT count(*) from employee;


Below example returns the number of employees per department. Here employees are grouped by the department.

SELECT department, count(department) from employee group by department;

department count
---------- -----
sales       2
accounts    2

Below query returns all the first_names that appears more than once. In this example count is used along with group by and having clauses.

SELECT first_name, count(first_name) from employee group by first_name having count(first_name)>1;

first_name count
----------  -----
john         2

Below example shows the syntax to count distinct values within a column. In our employee table, 2 employees have first name as ‘John’. Thus while counting the distinct first names, ‘John’ is counted only once.

SELECT count(distinct first_name) from employee;


Below example shows the syntax to count distinct values over multiple columns. As you can see, no 2 employees in our employee table has same first_name and last_name. Thus the count returned is 4. However if there is another employee ‘sam lee’ with id 105, the below example would still return 4 as ‘sam lee’ will be returned only once by the sub query due to distinct clause.

SELECT count(*) from (SELECT distinct first_name, last_name from employee) as a;