How does the SQL version of AVERAGEIF work?

The WHERE clause is the criterion; AVG(amount) averages the remaining rows. AVG skips null amounts, in the same way AVERAGEIF skips empty cells. Group by region to show the average for every region in one result.

Why can SQL give a different result from AVERAGEIF?

AVERAGEIF returns #DIV/0! when nothing matches; AVG returns null.

AVERAGEIF in SQL

AVERAGEIF averages the values that meet a condition; in SQL it is AVG with a WHERE clause.

The Excel version

=AVERAGEIF(B:B, "West", C:C)

The SQL equivalent

This example assumes: sales(id bigint, region text, amount numeric, sale_date date). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT AVG(amount) AS avg_west_sale
FROM sales
WHERE region = 'West';

How it works

The WHERE clause is the criterion; AVG(amount) averages the remaining rows.
AVG skips null amounts, in the same way AVERAGEIF skips empty cells.
Group by region to show the average for every region in one result.

Where the results differ from Excel

AVERAGEIF returns #DIV/0! when nothing matches; AVG returns null.
Text that looks like a number is ignored by Excel but is a type error (or must be cast) in SQL.
The average is of the rows, so duplicates in the data change it; use the median when a few large values dominate.

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

What is the average sale amount in the West region?
Compare average sale amount across regions.