Aggregate Functions - COUNT, SUM, AVG, MIN, MAX

Objectives

By the end of this lesson, you should be able to:

• Count rows with COUNT(*) and COUNT(column)
• Total and average numeric columns with SUM() and AVG()
• Find the smallest and largest values with MIN() and MAX()
• Explain what happens when these run without GROUP BY

💡 Why this matters: “How many employees are there,” “what’s the total payroll,” “who earns the most,” these questions need a single summary value calculated across many rows, not one row at a time. Aggregate functions do exactly that.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the same employees table as the rest of this module.

COUNT()

SELECT COUNT(*) AS total_employees FROM employees;

 total_employees
------------------
                8

COUNT(*) counts every row, regardless of what’s in any column. COUNT(column) counts only rows where that column is not NULL:

SELECT COUNT(department) AS employees_with_dept FROM employees;

 employees_with_dept
----------------------
                    7

There are 8 employees total, but only 7 have a department set, Devon’s is NULL (Module 6), and COUNT(department) skips it. This is a useful distinction: COUNT(*) answers “how many rows,” COUNT(column) answers “how many rows have a value here.”

SUM() and AVG()

SELECT SUM(salary) AS total_payroll, ROUND(AVG(salary), 2) AS average_salary
FROM employees;

 total_payroll | average_salary
----------------+-----------------
     578000.00 |        72250.00

SUM() adds up every value in the column, AVG() computes the mean. Both ignore NULL values entirely rather than treating them as zero, if salary had any NULL rows, they’d be excluded from both the total and the count used to calculate the average.

MIN() and MAX()

SELECT MIN(salary) AS lowest, MAX(salary) AS highest FROM employees;

  lowest  | highest
----------+----------
 58000.00 | 95000.00

MIN() and MAX() find the smallest and largest values in a column. They work on numbers, dates, and text alike, MIN(hire_date) would return the earliest hire date, MAX(last_name) the alphabetically last name.

Aggregates Without GROUP BY

Every example above returns exactly one row, even though employees has 8 rows. An aggregate function, used without GROUP BY, collapses the entire result set into a single summary row. This is the whole table treated as one group. The next module introduces GROUP BY, which splits the table into multiple smaller groups first (one per department, for example), then applies these same aggregate functions to each group separately, this lesson is the foundation that makes that possible.

Try It

1. Write a query for the total number of employees, using COUNT(*).
2. Write a query for the total number of employees who have a department set.
3. Write a query for the total, average, minimum, and maximum salary, all in one result row.
4. Write a query for the earliest hire_date among all employees, using MIN().

Recap

• COUNT(*) counts all rows, COUNT(column) counts only non-NULL values in that column.
• SUM() and AVG() total and average a numeric column, ignoring NULL values.
• MIN() and MAX() find the smallest and largest value, working on numbers, dates, and text.
• Without GROUP BY, an aggregate function collapses the whole result set into a single row.

Next lesson: type casting, converting a value from one data type to another.