COUNT is the most used aggregate function in SQL: how many rows, how many with a value, how many different values. It has three forms that answer three different questions, and the difference between them matters most when NULLs and outer joins are involved.
Code for This Guide
The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
count(*) count([all] expr) count(distinct expr)
| Form | Counts |
|---|---|
| COUNT(*) | Rows, whatever their values |
| COUNT(expr) | Rows where expr is not NULL |
| COUNT(DISTINCT expr) | Different non-NULL values of expr |
The Three Forms
The first query counts employees, those with a commission, and the departments they belong to, along with other basic aggregates. The second counts the staff of each department.
Example:
select count(*) as employees,
count(commission_pct) as with_commission,
count(distinct department_id) as departments,
sum(salary) as payroll,
round(avg(salary), 2) as avg_salary,
min(hire_date) as first_hire,
max(hire_date) as last_hire
from employees;
select department_id, count(*) as staff, sum(salary) as payroll, max(salary) as top_salary
from employees
group by department_id
order by department_id
fetch first 4 rows only;Output:
EMPLOYEES WITH_COMMISSION DEPARTMENTS PAYROLL AVG_SALARY FIRST_HIRE LAST_HIRE
____________ __________________ ______________ __________ _____________ ______________ ______________
55 7 10 803400 14607.27 01-MAR-2012 02-FEB-2026
DEPARTMENT_ID STAFF PAYROLL TOP_SALARY
________________ ________ __________ _____________
10 2 84000 48000
20 12 213800 27000
30 11 99100 22000
40 10 142100 3400055 employees, but only 7 with a commission: COUNT(commission_pct) skips the NULLs. COUNT(DISTINCT department_id) finds 10 departments.
COUNT After an Outer Join
The difference between COUNT(*) and COUNT(column) matters after an outer join. An airport without routes still produces one row, with NULLs in the route columns. COUNT(*) counts that row; COUNT(r.route_id) does not.
Example:
-- after an outer join, COUNT(*) counts the empty row; COUNT(column) does not
select a.airport_code, count(*) as count_star, count(r.route_id) as count_routes
from airports a left join routes r on r.origin = a.airport_code
where a.airport_code in ('DXB', 'KTM', 'NRT')
group by a.airport_code
order by a.airport_code;
-- over no rows, COUNT returns 0 and SUM returns NULL
select count(*) as cnt, sum(salary) as total from employees where department_id = 999;Output:
AIRPORT_CODE COUNT_STAR COUNT_ROUTES
_______________ _____________ _______________
DXB 20 20
KTM 1 0
NRT 2 2
CNT TOTAL
______ ________
0Kathmandu (KTM) has no routes: COUNT(*) says 1, COUNT(r.route_id) correctly says 0. Count a column of the optional table whenever you count matches through an outer join. The second query shows that COUNT returns 0 over no rows, while SUM returns NULL.
Things to Know
- COUNT(*) and COUNT(1) are the same; the optimizer treats them identically.
- COUNT never returns NULL, which makes it safe in arithmetic and comparisons.
- COUNT(DISTINCT a, b) is not allowed; count distinct combinations with COUNT(DISTINCT a || '|' || b) or a subquery.
- With FILTER (WHERE condition), one query can count several subsets of rows.
Related Guides
- How to Use GROUP BY in Oracle Database 23ai
- How to Filter Groups with HAVING in Oracle SQL
- How NULL Works in Oracle Conditions
Conclusion
COUNT(*) counts rows, COUNT(expr) counts non-NULL values, and COUNT(DISTINCT expr) counts different values. Count a column of the optional table after an outer join, and remember that COUNT returns 0, never NULL, when there are no rows.
