AVG()

AVG is like finding what a typical score is by adding all the scores and dividing by how many there were.

In this page:

Finding the Average
Averages with Conditions
NULL Values in AVG()
Rounding Averages
AVG() with GROUP BY

Syntax

sql

SELECT AVG(column_name) FROM table_name [WHERE condition];

Finding the Average

The AVG() function calculates the average (mean) value of a numeric column across the matched rows. It adds all non-NULL values and divides by the count of those values, giving a single representative figure.

Example: Finding the Average

sql

CREATE TABLE orders (id INT, total INT);
INSERT INTO orders VALUES (1, 100), (2, 200), (3, 300);
SELECT AVG(total) AS average_order FROM orders;

Averages with Conditions

You can target specific data using a WHERE clause before averaging. This calculates the average value only for rows that meet your criteria, such as the average order size for a single customer.

Example: Averages with Conditions

sql

CREATE TABLE orders (id INT, customer_id INT, total INT);
INSERT INTO orders VALUES (1, 1, 100), (2, 1, 200), (3, 2, 500);
SELECT AVG(total) AS avg_for_customer_1 FROM orders WHERE customer_id = 1;

NULL Values in AVG()

The AVG() function completely ignores NULL values in both the sum and the count it divides by. Rows with NULLs are not included in the sum or the dividing count, so they don't drag the average toward zero.

Example: NULL Values in AVG()

sql

CREATE TABLE orders (id INT, total INT);
INSERT INTO orders VALUES (1, 100), (2, NULL), (3, 300);
SELECT AVG(total) AS avg_total FROM orders;

Rounding Averages

Averages often return numbers with many decimal places, which can look messy in a report. You can combine AVG() with the ROUND() function to clean up the output to a fixed number of decimal places.

Example: Rounding Averages

sql

CREATE TABLE orders (id INT, total INT);
INSERT INTO orders VALUES (1, 100), (2, 201), (3, 305);
SELECT ROUND(AVG(total), 2) AS avg_rounded FROM orders;

AVG() with GROUP BY

You can group your averages by category instead of computing one overall figure. This displays the mean value for different categories or departments in your database, side by side in one result set.

Example: AVG() with GROUP BY

sql

CREATE TABLE orders (id INT, department TEXT, total INT);
INSERT INTO orders VALUES (1, 'Sales', 100), (2, 'Sales', 200), (3, 'IT', 500);
SELECT department, AVG(total) AS avg_total FROM orders GROUP BY department;

Chapter Quiz — Complete all 7 topics to unlock

0/7 topics done

Complete these topics first:

← Previous Lesson SUM() Next Lesson → MIN() & MAX()