Multi-Row Functions in SQL: Complete Guide with Examples

Quick answer: Learn multi-row (aggregate) functions in SQL including SUM, AVG, COUNT, MIN, MAX, GROUP BY and HAVING, with practical examples, NULL handling and interview questions.

SQL functions fall into two broad families. Single-row functions work on one row at a time and return one result per row. Multi-row functions, more commonly called aggregate functions, work on a group of rows and return a single summarised result.

Multi-row functions are the foundation of almost every report, dashboard and analytical query you will ever write. If you are learning SQL for a Data Analyst or Data Science role, this is one of the highest value topics to get genuinely comfortable with.

In this guide you will learn:

What multi-row functions are
The five core aggregate functions
How GROUP BY works
The difference between WHERE and HAVING
How NULL values are treated
Common mistakes and interview questions

What are Multi-Row Functions in SQL?

A multi-row function takes many rows as input and returns a single value. They are also called aggregate functions or group functions.

For example, a table with 10,000 sales rows can be reduced to one number, the total revenue, using a single aggregate function.

Single-row function	Multi-row function
Runs once per row	Runs once per group of rows
Returns one result per row	Returns one result per group
UPPER, ROUND, LENGTH	SUM, AVG, COUNT, MIN, MAX

Sample Table

All examples below use this Sales table.

Sale_ID	Region	Salesperson	Amount
1	North	Rahul	5000
2	North	Priya	7000
3	South	Aman	4000
4	South	Neha	9000
5	East	Vikas	NULL

The Core Aggregate Functions

1. SUM

Adds up all numeric values in a column.

SELECT SUM(Amount) AS total_sales
FROM Sales;

Result: 25000

2. AVG

Returns the average of the values.

SELECT AVG(Amount) AS average_sale
FROM Sales;

Result: 6250

Note that AVG divides by 4, not 5, because the NULL row is ignored. This catches a lot of people out and is covered in detail below.

3. COUNT

Counts rows. It behaves differently depending on what you pass to it.

SELECT
  COUNT(*)        AS all_rows,
  COUNT(Amount)   AS rows_with_amount,
  COUNT(DISTINCT Region) AS distinct_regions
FROM Sales;

Expression	Result	Behaviour
COUNT(*)	5	Counts every row, including NULLs
COUNT(Amount)	4	Counts only non-NULL values
COUNT(DISTINCT Region)	3	Counts unique non-NULL values

4. MIN

SELECT MIN(Amount) AS smallest_sale
FROM Sales;

Result: 4000

5. MAX

SELECT MAX(Amount) AS largest_sale
FROM Sales;

Result: 9000

MIN and MAX also work on text and dates, where they return the first and last value in sort order.

Using GROUP BY

An aggregate function on its own collapses the whole table into one row. GROUP BY lets you produce one summary row per category instead.

SELECT Region,
       SUM(Amount) AS region_total,
       COUNT(*)    AS number_of_sales
FROM Sales
GROUP BY Region;

Region	region_total	number_of_sales
North	12000	2
South	13000	2
East	NULL	1

The GROUP BY rule

Every column in the SELECT list must either appear in the GROUP BY clause or be wrapped in an aggregate function. Breaking this rule is one of the most common SQL errors beginners hit.

-- This fails: Salesperson is neither grouped nor aggregated
SELECT Region, Salesperson, SUM(Amount)
FROM Sales
GROUP BY Region;

HAVING vs WHERE

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. You cannot use an aggregate function in WHERE.

SELECT Region,
       SUM(Amount) AS region_total
FROM Sales
WHERE Amount IS NOT NULL      -- filters rows first
GROUP BY Region
HAVING SUM(Amount) > 12000;   -- filters groups after

WHERE	HAVING
Filters rows	Filters groups
Runs before GROUP BY	Runs after GROUP BY
Cannot contain aggregates	Designed for aggregates

How Aggregate Functions Treat NULL

This is the single most misunderstood part of the topic, and it comes up constantly in interviews.

All aggregate functions except COUNT(*) ignore NULL values entirely.

SUM ignores NULLs
AVG ignores NULLs in both the total and the divisor
COUNT(column) ignores NULLs
COUNT(*) counts every row regardless

This means AVG is not always the same as SUM divided by COUNT(*).

-- These two give DIFFERENT answers when NULLs exist
SELECT AVG(Amount)               AS avg_ignoring_nulls,
       SUM(Amount) / COUNT(*)    AS avg_treating_null_as_zero
FROM Sales;

If you want NULLs treated as zero, convert them explicitly.

SELECT AVG(COALESCE(Amount, 0)) AS avg_with_null_as_zero
FROM Sales;

Query Execution Order

Understanding the order SQL actually runs in explains most confusing errors.

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Because SELECT runs after GROUP BY and HAVING, a column alias defined in SELECT usually cannot be used in WHERE or HAVING, though many databases do allow it in ORDER BY.

A Practical Reporting Example

A realistic query combining everything above.

SELECT Region,
       COUNT(*)                    AS total_sales,
       SUM(Amount)                 AS revenue,
       ROUND(AVG(Amount), 2)       AS average_deal,
       MAX(Amount)                 AS largest_deal
FROM Sales
WHERE Amount IS NOT NULL
GROUP BY Region
HAVING COUNT(*) >= 2
ORDER BY revenue DESC;

Common Mistakes to Avoid

Using WHERE to filter an aggregate instead of HAVING
Forgetting that AVG ignores NULLs rather than treating them as zero
Selecting a column that is not in GROUP BY and not aggregated
Assuming COUNT(column) and COUNT(*) always match
Using COUNT(DISTINCT) without realising it also ignores NULLs

Interview Questions on Multi-Row Functions

What is the difference between single-row and multi-row functions?

Single-row functions return one result per row. Multi-row functions aggregate many rows into a single result per group.

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

COUNT(*) counts all rows including those with NULLs. COUNT(column) counts only rows where that column is not NULL.

Can you use an aggregate function in a WHERE clause?

No. WHERE is evaluated before grouping happens, so the aggregate does not exist yet. Use HAVING instead.

How do aggregate functions handle NULL?

They ignore NULL values, with the single exception of COUNT(*). Use COALESCE if you need NULLs treated as zero.

What is the difference between GROUP BY and a window function?

GROUP BY collapses rows into one row per group. A window function calculates across a group of rows but keeps every individual row in the output.

-- GROUP BY: 3 rows out
SELECT Region, SUM(Amount) FROM Sales GROUP BY Region;

-- Window function: all 5 rows out, each with its region total
SELECT Region, Amount,
       SUM(Amount) OVER (PARTITION BY Region) AS region_total
FROM Sales;

Final Thoughts

Multi-row functions are where SQL stops being a way to retrieve data and becomes a way to answer business questions. Almost every dashboard metric, KPI and analytical report is built from SUM, AVG, COUNT, MIN and MAX combined with GROUP BY.

Get genuinely comfortable with GROUP BY rules, the WHERE and HAVING distinction, and NULL behaviour. Those three areas account for the majority of both real world bugs and interview questions on this topic.