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

Most queries return rows. Aggregate functions return answers — one value distilled from many rows: how many orders, the total revenue, the average price, the cheapest and most expensive product. They’re how SQL turns a table into a summary.

Run them live below — a real SQL engine in your browser. Every aggregate collapses the whole products table into a single row of numbers.

The five you’ll use constantly

Each takes a column (or *) and returns one value across all the rows the query selected:

SELECT SUM(amount) AS revenue, AVG(amount) AS avg_order
FROM orders;

Give each result a readable name with AS — SUM(amount) AS revenue — and your output reads like a report instead of a formula.

COUNT, and the NULL detail

COUNT has two forms that differ in one important way:

• COUNT(*) counts rows — every row, always.
• COUNT(column) counts non-NULL values in that column — rows where the column is empty don’t count.

That gap is often exactly what you want to measure. In the sample data one customer has no segment, so COUNT(segment) comes back one short of COUNT(*):

COUNT(DISTINCT column) goes further — it counts the unique values. And aggregates ignore NULL across the board: AVG, SUM, MIN, and MAX all skip empty values rather than treating them as zero.

Per-group answers with GROUP BY

An aggregate over a whole table gives you one number. Add GROUP BY and you get one number per group — the average price per category, the order count per status:

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;

The rule: every column in your SELECT must either be in the GROUP BY or wrapped in an aggregate. (Ask for category and price while grouping by category and SQL rightly objects — which price would it show for the whole group?)

Filtering groups with HAVING

WHERE filters rows before they’re grouped. To filter on an aggregate — “only categories whose total exceeds $150” — you need HAVING, which filters after grouping:

SELECT category, SUM(amount) AS total
FROM orders
GROUP BY category
HAVING SUM(amount) > 150;

WHERE can’t see SUM(amount) (the sum doesn’t exist until the group is formed), so this is HAVING’s job. You can use both together: WHERE to pick which rows enter the groups, HAVING to pick which groups survive.

Run “Try to use WHERE (it fails)” to see the error — a good reminder of which clause aggregates belong in.

Practice

Frequently asked questions

What are aggregate functions in SQL? Functions that compute a single value from a set of rows — COUNT, SUM, AVG, MIN, and MAX. They summarize data instead of returning individual rows.

What’s the difference between COUNT(*) and COUNT(column)? COUNT(*) counts every row; COUNT(column) counts only rows where that column is not NULL.

Do aggregate functions include NULL values? No. SUM, AVG, MIN, MAX, and COUNT(column) all ignore NULLs. AVG divides by the count of non-NULL values, not the total rows.

What’s the difference between WHERE and HAVING? WHERE filters individual rows before grouping; HAVING filters groups after aggregation. Conditions on aggregate functions must go in HAVING.

Can I use multiple aggregates in one query? Yes — SELECT COUNT(*), AVG(price), MAX(price) FROM products returns all three in a single row.

From summaries to dashboards

Aggregates are the math behind every metric and dashboard. Doing it once in a query is easy; doing it continuously across live sources is the work — and it’s what ET1 automates, with a Group By / Aggregation node that computes these same SUM/AVG/COUNTs on a visual canvas and feeds them straight into a chart.

Keep going: the full GROUP BY guide, the SELECT statement these build on, or filtering rows first with the WHERE clause.