Calculating Averages with AVG

A frequent requirement in data analysis is to find the average value of a numeric column. Whether you need the average order total, the mean customer satisfaction score, or the typical session duration, the AVG() function is the tool for the job. It calculates the arithmetic mean of a set of numbers.

Basic Usage of AVG()

The AVG() function works by summing all the non-null numeric values in a specified column and then dividing by the count of those non-null values.

The basic syntax is straightforward:

SELECT AVG(column_name)
FROM table_name;

Let's consider an orders table containing information about customer purchases:

order_id	customer_id	order_date	order_total
101	5	2023-10-26	150.75
102	12	2023-10-26	85.00
103	5	2023-10-27	210.50
104	21	2023-10-27	NULL
105	12	2023-10-28	110.25

To find the average order_total across all completed orders (ignoring the one with a NULL total), you would write:

SELECT AVG(order_total)
FROM orders;

The database performs this calculation: $(150.75 + 85.00 + 210.50 + 110.25) / 4 = 556.50 / 4 = 139.125$.

The result might look something like this:

avg
139.125000

The exact number of decimal places might vary depending on the database system and the data type of the order_total column.

Using Aliases with AVG()

Like other functions and columns, the default output column name (often avg or similar) might not be very descriptive. You can assign a more meaningful name using an alias with the AS keyword:

SELECT AVG(order_total) AS average_order_value
FROM orders;

This query produces a much clearer result:

average_order_value
139.125000

Calculating Averages for Subsets using WHERE

Often, you'll want to calculate the average for only a specific portion of your data. You can combine AVG() with the WHERE clause to filter the rows before the average is calculated.

For example, to find the average order total specifically for customer_id 12:

SELECT AVG(order_total) AS avg_order_total_cust12
FROM orders
WHERE customer_id = 12;

This query first filters the orders table to include only rows where customer_id is 12:

order_id	customer_id	order_date	order_total
102	12	2023-10-26	85.00
105	12	2023-10-28	110.25

Then, it calculates the average order_total for these rows: $(85.00 + 110.25) / 2 = 195.25 / 2 = 97.625$.

The result is:

avg_order_total_cust12
97.625000

Important Note on NULL Values

It's significant to remember that AVG(), like most aggregate functions (except COUNT(*)), ignores NULL values entirely. It calculates the sum of non-NULL values and divides by the count of non-NULL values. In our initial example, the row with order_id 104 was excluded from both the sum and the count.

Be mindful of this behavior. If NULL actually represents zero in your data context, simply using AVG() will give you the average of the non-zero values, which might not be what you intend. If you need to treat NULLs as zero in an average calculation, you would typically need to use a function to replace NULL with 0 before aggregation, but that's a more advanced technique. For now, just remember that AVG() computes the average of the present values.

Data Type Notes

The AVG() function is designed for numeric data types, such as INTEGER, DECIMAL, FLOAT, NUMERIC, etc. Attempting to use AVG() on non-numeric columns (like text or dates) will typically result in an error.

Calculating averages is a fundamental step in summarizing data. AVG() provides a direct way to compute the mean for an entire column or specific subsets defined by a WHERE clause. Later, you'll see how to calculate averages for multiple groups simultaneously using the GROUP BY clause.