SQL Aggregate Functions

SQL aggregate functions take many rows and return one summary value. They answer questions like "how many orders do we have?" or "what is the average product price?".

The five you will use daily are COUNT, SUM, AVG, MIN and MAX.

COUNT

COUNT(*) counts rows. Here we count the products in the Snacks category.

SELECT COUNT(*) AS snack_products
FROM Products
WHERE Category = 'Snacks';

snack_products
--------------
2
(1 row)

SUM and AVG

SUM adds up values and AVG calculates the mean. ROUND makes the average easier to read.

SELECT SUM(Quantity) AS total_units,
       ROUND(AVG(Quantity), 2) AS avg_units
FROM Orders;

total_units | avg_units
------------+----------
155         | 15.5
(1 row)

MIN and MAX

MIN and MAX find the smallest and largest values. They work on numbers, text and dates.

SELECT MIN(Price) AS cheapest,
       MAX(Price) AS most_expensive,
       MIN(ProductName) AS first_alphabetically
FROM Products;

SELECT MIN(OrderDate) AS first_order,
       MAX(OrderDate) AS last_order
FROM Orders;

cheapest | most_expensive | first_alphabetically
---------+----------------+---------------------
4.25     | 22             | Almonds
(1 row)

first_order | last_order
------------+-----------
2026-01-05  | 2026-05-22
(1 row)

Several Aggregates at Once

You can mix aggregates in one SELECT. This gives a quick summary of the product catalogue.

SELECT COUNT(*) AS products,
       SUM(Stock) AS units_in_stock,
       ROUND(AVG(Price), 2) AS avg_price
FROM Products;

products | units_in_stock | avg_price
---------+----------------+----------
8        | 1305           | 12.25
(1 row)

Rules to Remember

• Aggregates ignore NULL values (except COUNT(*))
• You cannot mix an aggregate and a normal column in SELECT without GROUP BY
• Use WHERE to filter rows before aggregating and HAVING to filter after

Frequently asked questions

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

COUNT(*) counts every row. COUNT(column) counts only rows where that column is not NULL.

Why does AVG of integers return a whole number?

In SQL Server and PostgreSQL, AVG of an integer column can use integer arithmetic. Cast to a decimal first, for example AVG(CAST(qty AS DECIMAL(10,2))). SQLite and MySQL return decimals.

Can I use aggregate functions in WHERE?

No. WHERE runs before grouping. Filter on aggregates with HAVING instead.