Aggregate Functions

Overview

Aggregate functions take a set of rows and return a single scalar value. The five core aggregates are COUNT, SUM, AVG, MIN, and MAX. - COUNT(*) counts all rows; COUNT(column) counts non-NULL values in that column. - SUM and AVG work on numeric columns and ignore NULLs. - MIN and MAX work on any orderable type (numbers, strings, dates). Without GROUP BY, an aggregate collapses the entire table into one row. With GROUP BY, each group gets its own aggregate result. Against the shared dataset: SELECT COUNT(*) AS total_products, AVG(price) AS avg_price, MIN(price) AS cheapest, MAX(price) AS most_expensive FROM products; Returns: 6 | 85.82333... | 4.99 | 199.99 — one summary row from six product rows. Think of aggregates as squeezers: they take a bag of values and squeeze out one number.

Aggregates are the building blocks of every business metric — total revenue, average order size, highest salary, number of active users. Without them you can only list individual rows, never summarize.

Where used: KPI dashboards, summary API responses, data quality checks

Why learn this

• Computing any business metric — totals, averages, counts — requires aggregate functions
• Understanding the difference between COUNT(*) and COUNT(column) prevents subtle NULL-related bugs

Query walkthrough

SELECT category,
       COUNT(*) AS num_products,
       MIN(price) AS cheapest,
       MAX(price) AS priciest
FROM products
GROUP BY category
ORDER BY category;

Focus: Watch 6 product rows collapse into 3 category groups, each summarised by COUNT, MIN, and MAX.

Common mistakes

• Using COUNT(*) when you mean COUNT(column): COUNT(*) counts all rows including those where the column of interest is NULL. COUNT(country) on the customers table returns 7 (skipping Emma's NULL country), while COUNT(*) returns 8. Use COUNT(column) when you want to count non-NULL values only.
• Using SUM when you mean COUNT: SUM adds up the values in a numeric column; COUNT counts how many rows exist. SUM(quantity) from order_items gives the total units sold (19), while COUNT(*) gives the number of line items (11). Mixing these up produces nonsensical metrics.
• Forgetting that AVG ignores NULLs: AVG skips NULL values entirely — it divides by the count of non-NULL values, not by the total row count. If 3 out of 5 rows have values 10, 20, 30 and 2 are NULL, AVG returns 20 (sum 60 / 3 non-NULLs), not 12 (60 / 5). Use COALESCE to turn NULLs into 0 if you want them included in the denominator.

Glossary

scalar

A single, one-dimensional value, like a number or a string, rather than a list or a table of values.

denominator

The bottom number in a fraction or division calculation that tells you how many parts the whole is divided into.

metric

A measurable value used to track and assess the status or performance of a specific business process, like total revenue or user count.

Recall questions

• What is the difference between COUNT(*) and COUNT(column)?
• What happens when you use an aggregate function without GROUP BY?
• How does AVG handle NULL values?

Understanding checks

What does this query return?

star_count = 8, country_count = 7

COUNT(*) counts all 8 customer rows. COUNT(country) skips Emma (id 5) whose country is NULL, returning 7. This is the classic difference between the two COUNT forms.

What rows does this return?

Electronics | 269.97 | 3 Furniture | 239.98 | 2 Stationery | 4.99 | 1

Products are grouped by category. Electronics: Keyboard(49.99) + Mouse(19.99) + Monitor(199.99) = 269.97, 3 products. Furniture: Desk(149.99) + Chair(89.99) = 239.98, 2 products. Stationery: Notebook(4.99), 1 product.

Practice tasks

Add SUM and AVG to the summary

This query shows COUNT and MIN per category. Add a SUM(price) column aliased as total_price and an AVG(price) column aliased as avg_price. Keep the existing columns and ordering.

Challenge

Total quantity sold per product

Write a query that shows each product name and the total quantity sold across all order items. Only include products that appear in order_items. Order by total quantity descending, then by name.

Continue learning

Previous: GROUP BY

Next: HAVING vs WHERE

Return to SQL Roadmap