SQL - AVG() Function with Examples
Aggregate functions form the backbone of SQL analytics, transforming rows of raw data into meaningful summaries. Among these, AVG() stands out as one of the most frequently used—calculating the...
Key Insights
- AVG() automatically ignores NULL values in its calculations, which affects both the sum and the count—understanding this behavior prevents subtle bugs in your reports
- Combining AVG() with GROUP BY and HAVING gives you powerful filtering capabilities, letting you find groups that exceed or fall below performance thresholds
- Window functions with AVG() unlock advanced analytics like comparing individual records against their group average without collapsing your result set
Aggregate functions form the backbone of SQL analytics, transforming rows of raw data into meaningful summaries. Among these, AVG() stands out as one of the most frequently used—calculating the arithmetic mean of numeric values across a set of rows.
The function’s syntax is deceptively simple:
AVG(expression)
But mastering AVG() means understanding its nuances: how it handles NULL values, how it interacts with GROUP BY, and how to combine it with other functions for sophisticated analysis. Let’s work through practical examples that cover real-world scenarios.
Basic Usage with Single Column
The most straightforward use of AVG() calculates the mean of all values in a numeric column. Consider an employees table:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10, 2),
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Alice Chen', 'Engineering', 95000.00, '2021-03-15'),
(2, 'Bob Martinez', 'Engineering', 87000.00, '2022-01-10'),
(3, 'Carol White', 'Sales', 72000.00, '2020-08-22'),
(4, 'David Kim', 'Engineering', 110000.00, '2019-06-01'),
(5, 'Eva Johnson', 'Sales', NULL, '2023-02-28'),
(6, 'Frank Brown', 'Marketing', 68000.00, '2021-11-15');
SELECT AVG(salary) AS average_salary
FROM employees;
Result:
average_salary
--------------
86400.00
Notice that Eva Johnson has a NULL salary. Here’s the critical behavior: AVG() ignores NULL values entirely. It doesn’t treat them as zero—it excludes them from both the numerator and denominator. The calculation above is (95000 + 87000 + 72000 + 110000 + 68000) / 5 = 86400, not divided by 6.
This distinction matters. If you need NULL values treated as zero, explicitly handle them:
SELECT AVG(COALESCE(salary, 0)) AS average_with_nulls_as_zero
FROM employees;
Result:
average_with_nulls_as_zero
--------------------------
72000.00
Now the calculation includes all six employees, with Eva’s NULL converted to zero.
AVG() with WHERE Clause
Filtering data before aggregation is a common requirement. The WHERE clause executes before AVG() calculates its result:
-- Average salary for Engineering department only
SELECT AVG(salary) AS avg_engineering_salary
FROM employees
WHERE department = 'Engineering';
Result:
avg_engineering_salary
----------------------
97333.33
You can combine multiple conditions:
-- Average salary for employees hired in 2021 or later
SELECT AVG(salary) AS avg_recent_hire_salary
FROM employees
WHERE hire_date >= '2021-01-01'
AND salary IS NOT NULL;
This pattern is essential for time-based reporting. Want the average order value for Q4? Filter by date range. Need average response time for a specific endpoint? Filter by URL path.
AVG() with GROUP BY
Real analysis rarely involves a single average across all data. You typically need averages broken down by category. GROUP BY makes this possible:
SELECT
department,
AVG(salary) AS avg_salary,
COUNT(*) AS employee_count
FROM employees
WHERE salary IS NOT NULL
GROUP BY department
ORDER BY avg_salary DESC;
Result:
department | avg_salary | employee_count
-------------|------------|---------------
Engineering | 97333.33 | 3
Sales | 72000.00 | 1
Marketing | 68000.00 | 1
Let’s look at a more realistic example with an orders table:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
region VARCHAR(50),
order_total DECIMAL(10, 2),
order_date DATE
);
INSERT INTO orders VALUES
(1, 101, 'North', 250.00, '2024-01-15'),
(2, 102, 'South', 175.00, '2024-01-16'),
(3, 101, 'North', 320.00, '2024-01-17'),
(4, 103, 'North', 89.00, '2024-01-18'),
(5, 102, 'South', 445.00, '2024-01-19'),
(6, 104, 'West', 210.00, '2024-01-20'),
(7, 101, 'North', 180.00, '2024-01-21');
-- Average order value by region
SELECT
region,
AVG(order_total) AS avg_order_value,
SUM(order_total) AS total_revenue,
COUNT(*) AS order_count
FROM orders
GROUP BY region
ORDER BY avg_order_value DESC;
Result:
region | avg_order_value | total_revenue | order_count
-------|-----------------|---------------|------------
South | 310.00 | 620.00 | 2
North | 209.75 | 839.00 | 4
West | 210.00 | 210.00 | 1
AVG() with DISTINCT
Sometimes you need the average of unique values only. DISTINCT eliminates duplicates before AVG() performs its calculation:
-- Sample table with repeated values
CREATE TABLE daily_temperatures (
reading_id INT,
sensor_id INT,
temperature DECIMAL(5, 2),
reading_date DATE
);
INSERT INTO daily_temperatures VALUES
(1, 1, 72.5, '2024-01-15'),
(2, 2, 72.5, '2024-01-15'),
(3, 3, 68.0, '2024-01-15'),
(4, 1, 72.5, '2024-01-15'),
(5, 2, 75.0, '2024-01-15');
-- Compare regular AVG vs AVG with DISTINCT
SELECT
AVG(temperature) AS avg_all,
AVG(DISTINCT temperature) AS avg_distinct
FROM daily_temperatures;
Result:
avg_all | avg_distinct
--------|-------------
72.10 | 72.00
The regular AVG includes all five readings. AVG(DISTINCT temperature) only considers the three unique values: 72.5, 68.0, and 75.0.
When should you use DISTINCT? It’s useful when duplicate values represent redundant data rather than legitimate repeated measurements. Be cautious—in most cases, you want all values included. Using DISTINCT incorrectly can produce misleading results.
Combining AVG() with Other Functions
Raw averages often need formatting or additional filtering. ROUND() handles decimal precision:
SELECT
department,
ROUND(AVG(salary), 2) AS avg_salary_rounded,
ROUND(AVG(salary), 0) AS avg_salary_whole
FROM employees
WHERE salary IS NOT NULL
GROUP BY department;
The HAVING clause filters groups based on aggregate results—something WHERE cannot do:
-- Find departments with average salary above 80000
SELECT
department,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
WHERE salary IS NOT NULL
GROUP BY department
HAVING AVG(salary) > 80000;
Result:
department | avg_salary
------------|----------
Engineering | 97333.33
This pattern is invaluable for finding outliers: customers with unusually high average order values, products with low average ratings, or regions with above-average performance.
-- Customers with average order value above overall average
SELECT
customer_id,
ROUND(AVG(order_total), 2) AS customer_avg
FROM orders
GROUP BY customer_id
HAVING AVG(order_total) > (SELECT AVG(order_total) FROM orders);
Practical Use Cases
Let’s examine real-world scenarios where AVG() drives meaningful insights.
Comparing Individual Values to Group Average
A common requirement: show each record alongside its group’s average. Window functions make this elegant:
SELECT
name,
department,
salary,
ROUND(AVG(salary) OVER (PARTITION BY department), 2) AS dept_avg,
ROUND(salary - AVG(salary) OVER (PARTITION BY department), 2) AS diff_from_avg
FROM employees
WHERE salary IS NOT NULL
ORDER BY department, salary DESC;
Result:
name | department | salary | dept_avg | diff_from_avg
--------------|-------------|-----------|-----------|---------------
David Kim | Engineering | 110000.00 | 97333.33 | 12666.67
Alice Chen | Engineering | 95000.00 | 97333.33 | -2333.33
Bob Martinez | Engineering | 87000.00 | 97333.33 | -10333.33
Frank Brown | Marketing | 68000.00 | 68000.00 | 0.00
Carol White | Sales | 72000.00 | 72000.00 | 0.00
This preserves individual rows while adding aggregate context—impossible with GROUP BY alone.
Running Averages for Trend Analysis
Track how averages change over time:
SELECT
order_date,
order_total,
ROUND(AVG(order_total) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS moving_avg_3day
FROM orders
ORDER BY order_date;
This calculates a 3-day moving average, smoothing out daily fluctuations to reveal trends.
Performance Metrics Dashboard
Combine multiple AVG() calculations for comprehensive reporting:
SELECT
region,
COUNT(*) AS total_orders,
ROUND(AVG(order_total), 2) AS avg_order_value,
ROUND(AVG(order_total) * COUNT(*), 2) AS calculated_revenue,
ROUND(
100.0 * AVG(order_total) / (SELECT AVG(order_total) FROM orders),
1
) AS pct_of_overall_avg
FROM orders
GROUP BY region
ORDER BY avg_order_value DESC;
This single query produces a region performance summary with averages contextualized against the overall baseline.
Final Thoughts
AVG() appears simple but powers sophisticated analysis when combined with SQL’s other features. Remember these key points: NULL handling is automatic but might not match your intent, GROUP BY transforms single averages into categorical breakdowns, and window functions let you blend individual records with aggregate calculations.
Start with basic averages, add filtering with WHERE and HAVING, then graduate to window functions for advanced analytics. Master these patterns, and you’ll extract meaningful insights from any dataset.