COUNT(): Count Rows
The COUNT() function returns the number of rows that match your query.
It is one of the most common aggregate functions. An aggregate function looks at many rows and returns one value.
You can use COUNT() in two ways:
| Syntax | Example | What it counts |
|---|---|---|
| COUNT(*) | SELECT COUNT(*) FROM employees; | All rows |
| COUNT(column) | SELECT COUNT(gender) FROM employees; | Rows where the column is not NULL |
NULL means "no value". You will learn more about it in the Manage Data section. Our employees table has no NULL values yet, so both ways give the same number here.
Example 1: Total Number of Employees
SELECT COUNT(*) FROM employees;
Output:
count
-------
20
(1 row)
There are 20 rows in the employees table. Notice that psql names the result column count, after the function.
Example 2: Name the Result with AS
Use AS to give a result column a clearer name. This is called an alias:
SELECT COUNT(*) AS total_employees FROM employees;
Output:
total_employees
-----------------
20
(1 row)
An alias changes only the name shown in the result. It does not change the table.
Example 3: Count Employees in the IT Department
SELECT COUNT(*) AS it_count FROM employees
WHERE department = 'IT';
Output:
it_count
----------
6
(1 row)
WHERE runs first and keeps only the IT rows. Then COUNT(*) counts them.
Example 4: Count Employees per Department
SELECT department, COUNT(*) AS employees
FROM employees
GROUP BY department
ORDER BY department;
Output:
department | employees
------------+-----------
Finance | 4
HR | 2
IT | 6
Marketing | 2
Sales | 6
(5 rows)
PostgreSQL Tip: FILTER
PostgreSQL lets you add a condition to one aggregate with FILTER. This way, you can count different things in the same query:
SELECT COUNT(*) AS total,
COUNT(*) FILTER (WHERE age > 30) AS over_30
FROM employees;
Output:
total | over_30
-------+---------
20 | 6
(1 row)
Tip
COUNT(*)counts all rows.COUNT(column)counts only the non-NULL values in that column.
In the exercise, count the female employees.