AVG() in SQL
Introduction
AVG() is an aggregate function that calculates the arithmetic mean of numeric expression values. It supports questions such as average salary, order value, product rating, API response time, test duration, defect resolution time, temperature, and unit price. The function can summarize an entire filtered result, produce one average per group, or calculate partitioned and moving averages as a window function.
SELECT AVG(salary) AS average_salary
FROM employees;
If salaries are 50,000, 60,000, and 70,000, the average is 60,000. The arithmetic is simple, but trustworthy averages require the correct input entity, denominator, NULL policy, weighting, join grain, precision, and reporting context.
Basic Syntax
AVG(numeric_expression)
AVG is normally applied to salary, price, score, rating, duration, response time, quantity, temperature, revenue, or another numeric expression. The function evaluates the expression for qualifying rows and calculates the mean of its non-NULL results.
A Basic Example
| employee_id | salary |
|---|---|
| 1 | 50000 |
| 2 | 60000 |
| 3 | 70000 |
SELECT AVG(salary) AS average_salary
FROM employees;
The calculation is (50,000 + 60,000 + 70,000) / 3 = 60,000. A descriptive alias makes the aggregate result clear to applications and report readers.
The AVG Formula
AVG(column)
is conceptually
SUM(column) / COUNT(column)
The same non-NULL inputs must be used in the numerator and denominator. For 10, 20, 30, and 40, SUM is 100, COUNT is 4, and AVG is 25. AVG expresses this intent more clearly than manually dividing aggregates.
AVG() Ignores NULL
For values 50,000, 60,000, NULL, and 70,000, AVG uses the three known salaries and returns 60,000. NULL is excluded from both the total and count. It is not treated as zero.
NULL Does Not Mean Zero
For ratings 5, 4, NULL, and 3, ordinary AVG returns 4 because it averages 5, 4, and 3. If the NULL were replaced by zero, the result would be 3. Those answers represent different business meanings: average known rating versus average across all rows with missing ratings scored as zero.
AVG with COALESCE
SELECT AVG(COALESCE(rating, 0)) AS average_rating
FROM reviews;
This includes missing ratings as zero. Use it only when zero is the defined value for missing data. In most review systems, an unrated item is not a zero-star review, so ordinary AVG is usually more accurate.
All NULL Values and Empty Results
If every expression result is NULL or no rows qualify, AVG generally returns NULL because no arithmetic mean exists.
SELECT AVG(bonus) AS average_bonus
FROM employees
WHERE department = 'Unknown';
A dashboard can use COALESCE(AVG(bonus), 0) if zero is the required display value, but it should not erase the distinction between no data and an observed average of zero.
Zero Is Included
AVG ignores NULL but includes numeric zero. For 10, 0, and 20, the average is 10. Zero is a known measurement and participates in both numerator and denominator.
Filtering Before AVG()
SELECT AVG(salary) AS average_it_salary
FROM employees
WHERE department = 'IT'
AND status = 'Active';
WHERE defines the input population before the average is calculated. The same pattern supports completed order value, active product price, successful API latency, or resolved defect duration.
AVG() with GROUP BY
SELECT department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department;
GROUP BY creates one input set per department. If IT salaries are 60,000 and 80,000, its average is 70,000. If HR salaries are 50,000 and 70,000, its average is 60,000. A NULL department forms a separate group unless excluded.
Common Grouped Averages
SELECT customer_id,
AVG(total_amount) AS average_order_value
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id;
The same structure calculates average product price by category, rating by product, response time by endpoint, test duration by suite, or salary by job title. Grouping columns establish the output grain.
Filtering Groups with HAVING
SELECT department,
AVG(salary) AS average_salary
FROM employees
WHERE status = 'Active'
GROUP BY department
HAVING AVG(salary) > 75000;
WHERE removes inactive employee rows. GROUP BY forms department sets. HAVING then keeps departments whose active-employee average exceeds 75,000. Aggregate thresholds belong in HAVING because they do not exist during row filtering.
AVG(DISTINCT expression)
For values 10, 10, 20, and 30, ordinary AVG is 17.5. AVG(DISTINCT value) averages 10, 20, and 30 and returns 20.
SELECT AVG(DISTINCT score) AS distinct_score_average
FROM results;
DISTINCT removes duplicate numeric values, not duplicate entities. Two employees may legitimately earn the same salary, so AVG(DISTINCT salary) usually does not represent the average employee salary.
Averaging Calculated Expressions
SELECT AVG(quantity * unit_price) AS average_line_value
FROM order_items;
This calculates the average value of an order-item line. It does not calculate average order value when an order can contain multiple lines. The expression is correct only for the metric named by the input row.
Query Grain Determines Meaning
Before calculating AVG, ask what one input row represents. An order row, item row, customer row, API request, daily summary, and product review are different entities. Averaging item lines answers a different question from averaging complete orders.
Correct Average Order Total from Items
SELECT AVG(order_total) AS average_order_value
FROM (
SELECT order_id,
SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
) AS order_totals;
The inner query restores one row per order. The outer query averages those order totals. This two-stage pattern is fundamental whenever the entity being averaged must first be derived from lower-grain records.
AVG() with JOIN
SELECT d.department_name,
AVG(e.salary) AS average_salary
FROM departments d
JOIN employees e
ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name;
This join retains one row per employee because each employee belongs to one department. The average therefore remains employee-grained.
Join Multiplication Distorts Averages
If employees are joined to projects, one employee can appear once per project. Employees with more projects receive more weight in AVG(e.salary). The result no longer answers average employee salary.
Do not apply AVG(DISTINCT salary) as a repair because different employees can share the same salary. Restore one row per employee through a subquery, CTE, or pre-aggregation before averaging.
Average Daily Sales
SELECT AVG(daily_sales) AS average_daily_sales
FROM (
SELECT DATE(order_date) AS sales_date,
SUM(total_amount) AS daily_sales
FROM orders
WHERE order_date >= :period_start
AND order_date < :period_end
GROUP BY DATE(order_date)
) AS daily_totals;
This averages sales-day totals. Dates with no orders are absent, so the result is average sales per active date. To average across every calendar date, join a calendar table and represent missing daily sales as zero.
Average Orders Per Customer
SELECT AVG(order_count) AS average_orders_per_customer
FROM (
SELECT c.customer_id,
COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
GROUP BY c.customer_id
) AS customer_orders;
The LEFT JOIN includes customers with zero orders. Averaging counts derived only from the orders table would exclude those customers and answer average orders among customers who ordered.
Average Order Value
SELECT AVG(total_amount) AS average_order_value
FROM orders
WHERE status = 'Completed';
This is valid when one row represents one qualifying order and total_amount contains the intended revenue basis. Refunds, taxes, discounts, and currency differences may require additional rules.
Equivalent SUM and COUNT
SELECT SUM(total_amount)
/ NULLIF(COUNT(total_amount), 0)
AS average_order_value
FROM orders;
The denominator is COUNT(total_amount), not COUNT(*), because SUM ignores NULL amounts. AVG is clearer for the ordinary arithmetic mean, while the expanded form helps explain or customize weighting.
Date-Filtered Averages
SELECT AVG(total_amount) AS todays_average_order
FROM orders
WHERE created_at >= CURRENT_DATE
AND created_at < CURRENT_DATE + INTERVAL 1 DAY;
SELECT AVG(total_amount) AS rolling_average_order
FROM orders
WHERE created_at >= NOW() - INTERVAL 7 DAY;
Today is a calendar range while the second query is a rolling seven-day window. For global reporting, derive local business-day boundaries in the storage zone.
Monthly Average Order Value
SELECT DATE_FORMAT(order_date, '%Y-%m') AS order_month,
AVG(total_amount) AS average_order_value
FROM orders
WHERE status = 'Completed'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY order_month;
The year-month key keeps January from different years separate. Include order count alongside the average so readers can judge sample size.
API Response Time
SELECT endpoint,
COUNT(*) AS request_count,
AVG(response_time_ms) AS average_response_ms
FROM api_logs
WHERE request_time >= NOW() - INTERVAL 1 HOUR
GROUP BY endpoint;
The average describes central workload cost but can hide rare slow requests. Operational reports should often include median, P95, P99, maximum, and error rate.
Test Execution Duration
SELECT browser,
COUNT(*) AS executions,
AVG(execution_seconds) AS average_seconds
FROM test_execution
WHERE status IN ('PASSED', 'FAILED')
GROUP BY browser;
Excluding skipped tests can be correct when they have no meaningful execution duration. The filter should be documented because it changes both sample and average.
Defect Resolution Time
SELECT severity,
COUNT(*) AS resolved_defects,
AVG(TIMESTAMPDIFF(
HOUR,
created_at,
resolved_at
)) AS average_resolution_hours
FROM defects
WHERE resolved_at IS NOT NULL
GROUP BY severity;
Unresolved defects lack completed resolution duration and are excluded. A separate open-age metric is needed to prevent long-running unresolved defects from disappearing from quality reporting.
Review Ratings and Sample Size
SELECT product_id,
COUNT(*) AS review_count,
ROUND(AVG(rating), 2) AS average_rating
FROM reviews
GROUP BY product_id
HAVING COUNT(*) >= 10
ORDER BY average_rating DESC, product_id;
A product with one five-star review is not directly comparable to one with thousands. A minimum sample threshold helps, while confidence intervals or Bayesian ratings can provide stronger ranking methods.
Simple Versus Weighted Average
A simple average gives every input row equal weight. A weighted average gives observations different importance.
weighted average
= SUM(value * weight) / SUM(weight)
Choosing between them is a business decision. Ordinary AVG is correct only when each input entity should contribute equally.
Weighted Average Price
SELECT SUM(price * quantity)
/ NULLIF(SUM(quantity), 0)
AS weighted_average_price
FROM sales;
For price 10 with quantity 100 and price 20 with quantity 10, the simple price average is 15, while the quantity-weighted average is approximately 10.91. The weighted result better reflects the price paid per unit.
Averaging Percentages
Suppose suite A passes 90 of 100 tests and suite B passes 1 of 2. The simple average of 90% and 50% is 70%, but the overall pass rate is 91 / 102, approximately 89.22%. These answer different questions.
SELECT 100.0
* SUM(CASE WHEN status = 'PASSED' THEN 1 ELSE 0 END)
/ NULLIF(COUNT(*), 0) AS pass_percentage
FROM test_execution;
Combine numerators and denominators when the requirement is an overall rate. Average percentages only when each group should receive equal weight.
The Average-of-Averages Problem
Department A has two employees with average salary 100, while department B has 100 employees with average salary 50. The unweighted average of department averages is 75, but that is not the overall employee average. Department A would receive the same influence as a department fifty times larger.
Use underlying rows or combine group sums and counts. Average of averages is valid only when equal group weighting is intentionally the metric.
Correct Roll-Up from Summaries
Store sum and count with every pre-aggregated average. Day 1 may have sum 1,000 and count 10; day 2 may have sum 9,000 and count 100. The correct combined average is 10,000 / 110, approximately 90.91, not the simple mean of 100 and 90.
SELECT SUM(value_sum)
/ NULLIF(SUM(value_count), 0)
AS combined_average
FROM daily_summary;
AVG() as a Window Function
SELECT employee_id,
department,
salary,
AVG(salary) OVER (
PARTITION BY department
) AS department_average
FROM employees;
GROUP BY would return one row per department. The window form preserves each employee and adds the department average to every row.
Comparing a Row to Its Group Average
SELECT employee_id,
department,
salary,
AVG(salary) OVER (
PARTITION BY department
) AS department_average,
salary - AVG(salary) OVER (
PARTITION BY department
) AS difference_from_average
FROM employees;
This supports compensation analysis, anomaly detection, product comparison, and performance benchmarking without collapsing detail rows.
Moving Average
SELECT sales_date,
daily_sales,
AVG(daily_sales) OVER (
ORDER BY sales_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_7_row_average
FROM daily_sales;
This averages seven rows, not necessarily seven calendar dates. Missing dates or multiple rows per date alter the period. Use one daily row per calendar date, often generated through a calendar table, when the requirement is a seven-day average.
Cumulative Average
SELECT order_id,
order_date,
total_amount,
AVG(total_amount) OVER (
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS cumulative_average
FROM orders;
The average evolves as each order is included. The explicit ROWS frame and unique tie-breaker make sequencing deterministic.
Window Frame Defaults
An ordered window without an explicit frame can use a RANGE-based default, causing rows with equal ordering values to share the same frame. Specify ROWS when the requirement is row-by-row movement, and ensure ORDER BY uniquely defines the intended sequence.
Numeric Precision
The return type and scale of AVG depend on the input expression and database system. An average of integer values may require fractional output. Monetary inputs should normally use exact DECIMAL or NUMERIC types, while scientific measurements may legitimately use approximate types.
Test the database return type and the application driver's mapping. Large sums inside the average can require more precision than one source value.
Rounding an Average
SELECT ROUND(AVG(rating), 2) AS average_rating
FROM reviews;
Calculate at full supported precision and round for the required output. Averaging values that were rounded prematurely can produce a different result. Financial and regulatory rules may specify a particular rounding stage and method.
Skew, Outliers, and Distribution
An average can be mathematically correct yet misleading. Response times of 100, 110, 120, 130, and 5,000 milliseconds have a high average driven by one outlier. Salaries and order values are often skewed as well.
Use median, percentiles, minimum, maximum, standard deviation, histograms, and sample count when distribution matters. AVG should not be the only performance or risk metric.
Trimmed and Capped Averages
Some analytical policies exclude outliers or cap extreme values before averaging. Such transformations must be explicit and governed because they change the population. Report both the method and number of excluded records; never silently remove inconvenient observations.
Time-Weighted Averages
Sensor values, balances, and utilization rates may persist for unequal durations. Averaging recorded rows gives frequent samples more influence, while a time-weighted average multiplies each value by the duration for which it applied.
SELECT SUM(measurement * duration_seconds)
/ NULLIF(SUM(duration_seconds), 0)
AS time_weighted_average
FROM measurement_intervals;
Intervals must not overlap or leave unintended gaps. Define how the final open interval ends at the report boundary.
Conditional Averages
CASE can select which values contribute to an average while retaining one shared source query.
SELECT AVG(CASE
WHEN status = 'Completed' THEN total_amount
END) AS completed_average,
AVG(CASE
WHEN status = 'Cancelled' THEN total_amount
END) AS cancelled_average
FROM orders;
Rows that do not match a branch produce NULL and are excluded from that AVG. This is useful for dashboards, but a WHERE clause is clearer when only one population is needed. Include counts for each condition so an average is not interpreted without its sample size.
Conditional Average Versus Zero-Filled CASE
These two expressions have different denominators:
AVG(CASE WHEN status = 'Completed'
THEN total_amount END)
AVG(CASE WHEN status = 'Completed'
THEN total_amount ELSE 0 END)
The first averages completed orders only. The second includes every order and gives noncompleted orders a zero value. The second is rarely an average order value; it is an average completed amount contribution per all input rows.
Missing-Data Bias
AVG's NULL exclusion is mathematically defined, but the observed population can still be biased. If unhappy customers are less likely to submit a rating, the average known rating may overstate satisfaction. If old devices fail to report slow measurements, average latency can appear better than reality.
Report missing-value count and collection coverage. SQL can calculate the observed mean, but domain analysis must determine whether the missingness mechanism makes it representative.
Average Across Dates with No Activity
An average of grouped daily totals normally excludes dates that have no rows. To include zero-activity dates, begin with a calendar and left join daily activity.
SELECT AVG(COALESCE(d.daily_sales, 0))
AS average_sales_per_calendar_date
FROM business_calendar c
LEFT JOIN (
SELECT DATE(order_date) AS sales_date,
SUM(total_amount) AS daily_sales
FROM orders
GROUP BY DATE(order_date)
) d ON d.sales_date = c.calendar_date
WHERE c.calendar_date >= :start_date
AND c.calendar_date < :end_date;
The zero comes from a known absence of sales on a calendar date, not an unknown order amount. That is a legitimate and explicit use of COALESCE.
Cohort and Segment Averages
Comparing average behavior across signup cohorts, regions, plans, or devices requires consistent eligibility rules and observation windows. A customer registered yesterday has had less opportunity to purchase than one registered a year ago.
SELECT signup_month,
COUNT(*) AS customer_count,
AVG(first_30_day_revenue) AS average_30_day_revenue
FROM customer_cohort_metrics
GROUP BY signup_month
ORDER BY signup_month;
A fixed 30-day observation window makes cohorts more comparable. Exclude incomplete windows or label them separately.
Currency and Unit Safety
An average is meaningful only when input values share compatible units. Averaging USD, EUR, and INR amounts directly produces no valid currency. Group by currency or convert every value using an approved exchange-rate policy and effective date.
SELECT currency_code,
AVG(amount) AS average_payment
FROM payments
GROUP BY currency_code;
Likewise, normalize seconds and milliseconds, Celsius and Fahrenheit, or kilograms and pounds before averaging.
Average Rates from Numerators and Denominators
Rates such as click-through rate, defect density, conversion rate, and requests per second should often be rolled up from their components rather than averaged from row percentages.
SELECT 100.0 * SUM(conversions)
/ NULLIF(SUM(visits), 0)
AS overall_conversion_rate
FROM campaign_daily_metrics;
This weights each row by visits. AVG(conversion_rate) instead gives a low-traffic row the same influence as a high-traffic row unless equal row weighting is intentional.
Average Duration and Censoring
Average resolution time based only on completed cases excludes unresolved work. This is called censoring in many analyses. The completed-case average may improve simply because difficult cases remain open.
Pair completed duration with open-case age, completion rate, and backlog size. More advanced service-level analysis can use survival methods, but at minimum the SQL report should expose what was excluded.
Stable Reference Time
Reports involving ongoing duration should capture one reference instant. Calling NOW repeatedly across several statements can produce inconsistent ages, especially around a date boundary.
SET @report_time = NOW();
SELECT AVG(TIMESTAMPDIFF(
HOUR,
created_at,
COALESCE(resolved_at, @report_time)
)) AS average_age_hours
FROM defects;
Record the report time and time zone in exported metadata so the result can be explained and reproduced.
Sampling and Population Averages
AVG computes the arithmetic mean of the rows supplied to it. It does not determine whether those rows are a random sample, a complete population, or a biased subset. LIMIT without a sampling design does not create a representative sample.
When approximate analytics use sampling, document the selection method, weight observations appropriately, and report uncertainty. Exact SQL arithmetic over a biased sample still produces a biased estimate.
Pre-Aggregated Average Storage
For scalable roll-up, store at least a sum and contributing count, not only the average. If NULL values are excluded, the count must represent non-NULL contributors. For weighted averages, store weighted sum and total weight.
| Metric | Stored components | Roll-up formula |
|---|---|---|
| Simple average | value_sum, value_count | SUM(value_sum) / SUM(value_count) |
| Weighted average | weighted_sum, weight_sum | SUM(weighted_sum) / SUM(weight_sum) |
| Rate | numerator, denominator | SUM(numerator) / SUM(denominator) |
This supports correct monthly, quarterly, and yearly aggregation even when lower-level groups have different sizes.
Incremental Refresh and Late Data
Daily summaries must account for late-arriving events, corrections, deletions, and status changes. Updating only the current day leaves historical sums and counts stale when yesterday's record arrives today.
Use a governed lookback, change-data capture, or targeted recomputation. Track summary freshness and reconcile selected periods against detailed source data.
Cross-Database Portability
Core aggregate and window AVG syntax is broadly available, but return types, decimal scale, integer promotion, frame defaults, FILTER syntax, date-difference functions, percentile support, and execution plans vary by database.
During migration, test empty and all-NULL inputs, integer averages, decimal precision, negative values, tied window ordering, weighted formulas, and driver mappings. Syntactic success does not guarantee matching numeric behavior.
Security and Tenant Scope
Every average must respect row-level authorization and tenant boundaries. An average computed before applying security filters can disclose information about other groups even if detail rows are hidden.
Apply tenant and access predicates at the source population and verify that cached or pre-aggregated results use the same scope. Small-group averages can also reveal sensitive individual values, so privacy policies may require minimum group sizes.
Auditability
A decision-critical average should identify its population, filters, sample count, NULL handling, units, weighting, time range, time zone, snapshot, rounding, and query version. Provide drill-down identifiers where appropriate so reviewers can reconcile the metric to contributing rows.
Performance and Indexing
AVG performance depends on rows scanned, filters, indexes, joins, groups, partitions, numeric types, and execution plan. An index can locate a date range, but an exact average still needs enough sum and count information for all qualifying values.
SELECT AVG(total_amount)
FROM orders
WHERE order_date >= '2026-09-01'
AND order_date < '2026-10-01';
Use EXPLAIN, avoid accidental join multiplication, and pre-aggregate high-volume historical data with both sum and count preserved.
Transaction Visibility
An AVG reflects rows visible under the transaction's isolation level. Concurrent inserts, corrections, and deletes can make two sessions observe different averages. Reports requiring reconciliation should identify a cutoff or consistent snapshot.
Common Mistakes
- Treating NULL as zero without a business rule.
- Forgetting that zero is included.
- Averaging rows at the wrong entity grain.
- Using AVG(DISTINCT) to hide join duplication.
- Averaging group averages without weighting by group size.
- Averaging percentages with unequal denominators.
- Using
SUM(column) / COUNT(*)when the column can be NULL. - Excluding customers or dates with zero activity unintentionally.
- Using a seven-row window as if it always meant seven days.
- Rounding source values too early.
- Ignoring numeric return type and precision.
- Using average alone when distribution and outliers matter.
Testing AVG Queries
Test empty input, all-NULL values, zero, positive and negative values, duplicate values, fractions, extreme values, and mixed NULL/zero data. For groups, include different sample sizes. For joins, include entities with zero, one, and many related rows.
For weighted averages, hand-calculate numerator and denominator. For windows, test missing dates, duplicate order values, partition boundaries, and frame edges. Compare pre-aggregated roll-ups with averages calculated from detail rows.
Production Checklist
- Define the entity being averaged.
- Confirm that the query has one input row per entity.
- Define whether NULL is excluded, invalid, or converted.
- Keep known zero values in the population.
- Choose simple or weighted averaging deliberately.
- Include sample count with reported averages.
- Do not average averages without correct weights.
- Repair join grain instead of using DISTINCT as a shortcut.
- Use exact types where exact arithmetic matters.
- Round at the governed reporting stage.
- Use explicit window frames and deterministic ordering.
- Pair AVG with distribution metrics when outliers matter.
Interview Questions and Answers
What is AVG()? It is an aggregate function that calculates the arithmetic mean of non-NULL numeric expression values.
Does AVG ignore NULL? Yes. NULL is excluded from both the total and denominator.
Does AVG ignore zero? No. Zero is a valid numeric value and participates in the average.
What happens when no values exist? AVG generally returns NULL.
AVG versus SUM? SUM returns the total; AVG returns that total divided by the number of contributing values.
How do you average by group? Group the input entity and apply AVG to each group.
What is a weighted average? It is SUM(value * weight) / SUM(weight).
Why can average of averages be wrong? Groups can contain different numbers or weights of observations.
How do you calculate a moving average? Use AVG as a window function with ORDER BY and an explicit frame.
Mental Model
For scores 80, 90, NULL, 70, and 100, the contributing values are 80, 90, 70, and 100. Their sum is 340, their non-NULL count is 4, and AVG is 85.
| Expression | Meaning | Result |
|---|---|---|
SUM(score) | Total known score | 340 |
COUNT(score) | Known score count | 4 |
AVG(score) | Average known score | 85 |
Key Takeaway
AVG() answers "what is the arithmetic mean?" It adds the relevant non-NULL values and divides by their non-NULL count, but the result is useful only when the rows represent the correct business entity.
RELIABLE AVERAGE = CORRECT ENTITY GRAIN
+ DEFINED POPULATION
+ INTENTIONAL NULL POLICY
+ CORRECT DENOMINATOR
+ APPROPRIATE WEIGHTING
+ JOIN-SAFE INPUT
+ SUFFICIENT PRECISION
+ DISTRIBUTION CONTEXT
Use WHERE to define the population, GROUP BY for category averages, HAVING for aggregate thresholds, weighted formulas when observations have unequal importance, and window AVG for comparisons and moving analysis. Always report sample size and consider whether median or percentiles describe the data better.