Skip to content
SQL

SQL Aggregate Functions: COUNT, SUM, AVG, MIN and MAX

Ihsan Ullah · 11 min read
SQL aggregate functions cover for Lesson 9 of the free SQL course: COUNT, SUM, AVG, MIN and MAX

SQL course · Module 3: Aggregation · Lesson 9 of 40

Quick answer: SQL aggregate functions turn many rows into one value. COUNT counts rows, SUM adds numbers, AVG gives the average, and MIN and MAX give the smallest and largest value: SELECT count(), avg(price) FROM products; returns one row. All of them except COUNT() skip NULL values, and when no rows match, every aggregate except COUNT returns NULL, so wrap SUM in COALESCE(..., 0) when you need a zero.

In this lesson you will learn:

  • what the five SQL aggregate functions return, and their result types in PostgreSQL
  • the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)
  • how to compute revenue with SUM(quantity * unit_price) and round an AVG
  • why SUM of no rows is NULL, and how COALESCE fixes it
  • how to count by condition with FILTER (and the CASE version for other databases)

Before you start: Lesson 8, SQL CASE WHEN, CAST and Common String and Date Functions.

We ran every example on PostgreSQL 16.15 with the shop database from Lesson 3. These functions are described the same way in the current PostgreSQL 18 manual, so they work the same on PostgreSQL 17 and 18.

What SQL aggregate functions do

Until now, every query returned one output row per table row. “An aggregate function computes a single result from multiple input rows” (PostgreSQL tutorial: Aggregate Functions):

SELECT count(*) AS orders FROM orders;
 orders 
--------
     22
(1 row)

The orders table has 22 rows, and the result is one row. You can use several aggregates in the same SELECT:

SELECT count(*) AS products, sum(price) AS sum_price, avg(price) AS avg_price,
       min(price) AS cheapest, max(price) AS dearest
FROM products;
 products | sum_price |      avg_price      | cheapest | dearest 
----------+-----------+---------------------+----------+---------
       10 |    712.64 | 71.2640000000000000 |     6.75 |  259.00
(1 row)
FunctionWhat it returns
count(*)the number of rows
count(column)the number of rows where the column is not NULL
sum(column)the total of the non-NULL values
avg(column)the average (mean) of the non-NULL values
min(column) / max(column)the smallest / largest non-NULL value

Source: PostgreSQL docs: Aggregate Functions. COUNT() and count() are the same; case does not matter.

COUNT(*), COUNT(column) and COUNT(DISTINCT column)

Lesson 6 showed that count(column) skips NULLs. DISTINCT inside the brackets counts each different value once:

SELECT count(*) AS customers, count(email) AS with_email,
       count(country) AS with_country, count(DISTINCT country) AS countries
FROM customers;
 customers | with_email | with_country | countries 
-----------+------------+--------------+-----------
        10 |          9 |           10 |         7
(1 row)

Emma Clarke has no email, so count(email) is 9. Every customer has a country, so count(country) is 10, but there are only 7 different countries, because Pakistan appears four times. The PostgreSQL manual says it in one line: count(*) “yields the total number of input rows”, count(f1) the rows “in which f1 is non-null”, and count(distinct f1) “the number of distinct non-null values of f1” (PostgreSQL docs: Aggregate Expressions).

COUNT(DISTINCT) answers questions like “how many customers have ordered?”:

SELECT count(*) AS orders, count(DISTINCT customer_id) AS customers_who_ordered FROM orders;
 orders | customers_who_ordered 
--------+-----------------------
     22 |                     9
(1 row)

22 orders came from 9 different customers. (Omar Haddad, customer 10, has never ordered.)

SUM: totals and revenue

SUM adds up a column, or an expression calculated for each row. In order_items, the money for one line is quantity * unit_price:

SELECT sum(quantity) AS units_sold, sum(quantity * unit_price) AS revenue FROM order_items;
 units_sold | revenue 
------------+---------
         65 | 2211.01
(1 row)

PostgreSQL first calculates quantity * unit_price for each of the 40 rows, then adds the 40 results. This total includes the two cancelled orders, because their status is stored in orders, not in order_items. Lesson 12 shows how to join the two tables and leave them out.

Check it on order 1, line by line:

SELECT order_id, quantity, unit_price, quantity * unit_price AS line_total
FROM order_items WHERE order_id = 1;
 order_id | quantity | unit_price | line_total 
----------+----------+------------+------------
        1 |        1 |      22.99 |      22.99
        1 |        2 |      12.50 |      25.00
(2 rows)
SELECT sum(quantity * unit_price) AS order_total FROM order_items WHERE order_id = 1;
 order_total 
-------------
       47.99
(1 row)

22.99 + 25.00 = 47.99. Note that sum(quantity) * sum(unit_price) would give a different, wrong number: multiply first, then add.

AVG and rounding

AVG divides the sum of the non-NULL values by how many there are. In PostgreSQL, the average of an integer column is a numeric, so the decimals are kept. Compare it with doing the same sum by hand:

SELECT avg(quantity) AS avg_qty, sum(quantity) / count(*) AS by_hand,
       round(avg(quantity), 2) AS avg_rounded
FROM order_items;
      avg_qty       | by_hand | avg_rounded 
--------------------+---------+-------------
 1.6250000000000000 |       1 |        1.63
(1 row)

sum(quantity) / count(*) is 65 / 40, but both are whole numbers, so this is the integer division from Lesson 8 and the result is 1. avg gives the right answer, 1.625. pg_typeof shows the types:

SELECT pg_typeof(count(*)) AS count_type, pg_typeof(sum(quantity)) AS sum_type,
       pg_typeof(avg(quantity)) AS avg_type
FROM order_items;
 count_type | sum_type | avg_type 
------------+----------+----------
 bigint     | bigint   | numeric
(1 row)

The manual lists avg(integer) as returning numeric and sum(integer) as returning bigint, a larger integer type that leaves room for big totals.

The long tail of zeros is not pretty in a report. round(value, 2) keeps two decimal places:

SELECT avg(price) AS avg_price, round(avg(price), 2) AS avg_price_rounded FROM products;
      avg_price      | avg_price_rounded 
---------------------+-------------------
 71.2640000000000000 |             71.26
(1 row)

MIN and MAX on numbers, dates and text

MIN and MAX work on numbers, text and dates (the manual lists many more sortable types). On a date column they give the first and last date:

SELECT min(order_date) AS first_order, max(order_date) AS last_order FROM orders;
 first_order | last_order 
-------------+------------
 2026-06-03  | 2026-10-30
(1 row)

On text they use alphabetical order. Hira Siddiqui’s city is NULL, and both functions skip it:

SELECT min(city) AS first_city, max(city) AS last_city, count(city) AS with_city FROM customers;
 first_city | last_city | with_city 
------------+-----------+-----------
 Berlin     | Toronto   |         9
(1 row)

The result is not NULL, even though one city is NULL. SUM and AVG skip NULLs in the same way: an average is over the non-NULL values only, so NULL is not treated as zero.

Aggregates with WHERE

WHERE runs first and picks the rows; the aggregates then summarise only those rows. Here is everything about the Wireless Mouse (product 1):

SELECT count(*) AS order_lines, sum(quantity) AS units, sum(quantity * unit_price) AS revenue,
       min(unit_price) AS lowest_price, max(unit_price) AS highest_price
FROM order_items
WHERE product_id = 1;
 order_lines | units | revenue | lowest_price | highest_price 
-------------+-------+---------+--------------+---------------
           6 |     9 |  218.91 |        22.99 |         24.99
(1 row)

MIN and MAX of unit_price show the price change built into the shop data from Lesson 3: the mouse sold for 22.99 in early orders and 24.99 later. Any condition from Lesson 5 works here.

When no rows match: NULL and COALESCE

There is no order 99. Watch what each aggregate returns:

SELECT count(*) AS lines, sum(quantity * unit_price) AS order_total, max(unit_price) AS top_price
FROM order_items
WHERE order_id = 99;
 lines | order_total | top_price 
-------+-------------+-----------
     0 |      (null) |    (null)
(1 row)

(We used \pset null '(null)' from Lesson 6 to show the NULLs.) You still get one row. count gives 0, but the others give NULL. The manual warns: “sum of no rows returns null, not zero as one might expect”, and suggests coalesce:

SELECT COALESCE(sum(quantity * unit_price), 0) AS order_total
FROM order_items
WHERE order_id = 99;
 order_total 
-------------
           0
(1 row)

Use it when a report or a program expects a number.

Counting by condition with FILTER

How many orders are delivered, and how many cancelled? WHERE can only pick one set of rows per query. PostgreSQL’s FILTER clause gives each aggregate its own condition: “only the input rows for which the filter_clause evaluates to true are fed to the aggregate function” (PostgreSQL docs: Aggregate Expressions).

SELECT count(*) AS all_orders,
       count(*) FILTER (WHERE status = 'delivered') AS delivered,
       count(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders;
 all_orders | delivered | cancelled 
------------+-----------+-----------
         22 |        16 |         2
(1 row)

Our SQLite 3.53.1 test accepted FILTER too, but MySQL 9.7 and SQL Server have no FILTER clause: it is not in the aggregate syntax of either manual (MySQL 9.7 manual: Aggregate Function Descriptions, Microsoft Learn: COUNT). The portable version uses CASE from Lesson 8:

SELECT count(*) AS all_orders,
       count(CASE WHEN status = 'delivered' THEN 1 END) AS delivered,
       sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;
 all_orders | delivered | cancelled 
------------+-----------+-----------
         22 |        16 |         2
(1 row)

A CASE without ELSE gives NULL for the other rows, and count skips NULLs. The sum version adds 1 for each match and 0 for the rest.

Dialect differences

The function names are the same in PostgreSQL, MySQL, SQLite and SQL Server. The result of AVG on an integer column is not:

DatabaseAVG of an integer column
PostgreSQLnumeric, decimals kept (1.625 above)
MySQL 9.7DECIMAL, decimals kept
SQLitea floating-point value (our test: 1.5 for 1, 2, 1, 2)
SQL Serverint, so the decimals are lost

Sources: MySQL 9.7 manual: Aggregate Function Descriptions (“return a DECIMAL value for exact-value arguments”), SQLite: Built-in Aggregate Functions and Microsoft Learn: AVG, whose return-type table lists int for an int input. In SQL Server, cast first, for example AVG(CAST(quantity AS decimal(10,2))). Lesson 2 compares these databases in more detail.

Try it yourself

Exercise 1: Delivered orders

Count the delivered orders, and how many different customers placed them.

Exercise 2: Headphones sales

For product 8 (Noise-Cancelling Headphones), show the units sold, the revenue, and the average line total rounded to 2 decimals.

Exercise 3: Orders per month in one row

In one query, count the orders placed in June 2026, in July 2026, and in total.

Solutions

Solution 1

SELECT count(*) AS delivered_orders, count(DISTINCT customer_id) AS customers
FROM orders
WHERE status = 'delivered';
 delivered_orders | customers 
------------------+-----------
               16 |         9
(1 row)

Ayesha (customer 1) has three delivered orders but is counted once.

Solution 2

SELECT sum(quantity) AS units, sum(quantity * unit_price) AS revenue,
       round(avg(quantity * unit_price), 2) AS avg_line
FROM order_items
WHERE product_id = 8;
 units | revenue | avg_line 
-------+---------+----------
     4 |  596.00 |   149.00
(1 row)

Every headphones line has quantity 1 at 149.00, so the average line is the price.

Solution 3

SELECT count(*) FILTER (WHERE order_date BETWEEN '2026-06-01' AND '2026-06-30') AS june,
       count(*) FILTER (WHERE order_date BETWEEN '2026-07-01' AND '2026-07-31') AS july,
       count(*) AS all_orders
FROM orders;
 june | july | all_orders 
------+------+------------
    4 |    5 |         22
(1 row)

Each FILTER has its own date range; count(*) without one still counts all rows. Lesson 10 shows a shorter way to count every month.

Common mistakes

  • A plain column next to an aggregate. SELECT status, count(*) FROM orders; fails with ERROR: column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function. One summary row cannot also show 22 statuses. Lesson 10 fixes this with GROUP BY.
  • An aggregate inside WHERE. SELECT name, price FROM products WHERE price = max(price); fails with ERROR: aggregate functions are not allowed in WHERE. For the most expensive product, use ORDER BY price DESC LIMIT 1 from Lesson 7 (it returns the Ergonomic Chair, 259.00). Lesson 11 explains why.
  • SUM or AVG of text. SELECT sum(name) FROM customers; fails with ERROR: function sum(text) does not exist. Of the five functions in this lesson, only count, min and max work on text.
  • Dividing SUM by COUNT yourself. With integer columns this drops the fraction (1 instead of 1.625). Use avg.
  • Expecting 0 from SUM. With no matching rows, sum returns NULL. Use COALESCE(sum(...), 0).

Frequently asked questions

What is the difference between COUNT(*) and COUNT(1)?

None in the result: 1 is never NULL, so count(1) counts every row, like count(*). count(*) is the form the PostgreSQL manual uses and the clearest to read. Only count(column) is different, because it skips NULLs.

Does AVG ignore NULL values in SQL?

Yes. AVG adds the non-NULL values and divides by how many non-NULL values there are, so rows with NULL are left out of both the sum and the count. If you want NULL to count as zero, write avg(COALESCE(column, 0)).

Can I use more than one aggregate function in a query?

Yes, as many as you need. Each one reads the rows that WHERE kept, and with FILTER (or CASE) each aggregate can use its own subset of them.

Summary

  • SQL aggregate functions turn many rows into one: count, sum, avg, min and max.
  • count(*) counts rows, count(column) skips NULLs, and count(DISTINCT column) counts different values.
  • Revenue is sum(quantity * unit_price); use avg instead of sum / count, and round(..., 2) for neat output.
  • All aggregates except count(*) skip NULLs, and all except count return NULL when no rows match; COALESCE(sum(...), 0) gives a zero.
  • FILTER (WHERE ...) counts by condition in PostgreSQL and SQLite; CASE inside the aggregate works everywhere.

Totals, averages and conditional counts are the base of most reports in the data analyst roadmap.

Next lesson

Next: Lesson 10, SQL GROUP BY and HAVING.

Sources

Ihsan Ullah

Written & tested by Ihsan Ullah

Data analyst with 3 years of experience and a Bachelor's degree in Computer Science. I write clear, practical guides on data, SQL, Python and AI.

More about me →

Leave a comment

Your email address will not be published. Required fields are marked *