SQL AVG

What is SQL AVG?

Returns the arithmetic mean of non-NULL numeric values in a result set or partition.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL AVG:

SQL AVG Full Explanation

AVG is a standard SQL aggregate and analytic function. As an aggregate, it scans all qualifying rows (or each GROUP BY group) and calculates the arithmetic mean of the supplied numeric expression. Nulls are ignored, while non-numeric data types must be implicitly convertible to a numeric type or the query fails. If no non-NULL rows exist, the result is NULL.AVG can also operate as a window function when used with an OVER clause. In this mode, it returns the average for the current row’s window frame while leaving other rows visible, enabling running averages, moving averages, and other advanced analytics.The result data type depends on the input and the dialect. Most engines promote integer inputs to decimal or floating types to preserve precision. DISTINCT may be applied to average only unique values. Performance can degrade on very large datasets without proper indexing or partitioning.

SQL AVG Syntax

-- Aggregate form
SELECT AVG([DISTINCT | ALL] numeric_expression) AS average_value
FROM table_name
[WHERE condition]
[GROUP BY columns];

-- Window form
SELECT AVG(numeric_expression) OVER (
        [PARTITION BY columns]
        [ORDER BY columns]
        [ROWS | RANGE frame_spec]
) AS moving_avg
FROM table_name;

SQL AVG Parameters

numeric_expression - Numeric or implicitly numeric column or expression to average.
DISTINCT (ALL) - Optional keyword to average only distinct values (default ALL).
OVER() - Optional analytic clause defining partitioning, ordering, and frame for window averages.

Example Queries Using SQL AVG

-- 1. Company-wide average salary
SELECT AVG(salary) AS avg_salary
FROM employees;

-- 2. Average salary per department
SELECT department_id, AVG(salary) AS dept_avg
FROM employees
GROUP BY department_id;

-- 3. Rolling 7-day average daily revenue (window function)
SELECT order_date,
       revenue,
       AVG(revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rev_7d_avg
FROM daily_revenue;

-- 4. Average unique scores, ignoring duplicates
SELECT AVG(DISTINCT score) AS avg_unique_score
FROM game_results;

Expected Output Using SQL AVG

Each query returns a single column containing the computed mean
In grouped or windowed queries, multiple rows return, each carrying its corresponding average value
If all rows in a group or window are NULL, the output for that row or group is NULL

Use Cases with SQL AVG

Calculate average order value for KPIs.
Monitor moving averages to smooth volatile metrics.
Compare average sensor readings across devices.
Derive benchmark averages for anomaly detection.
Feed aggregated averages into reporting dashboards.

Common Mistakes with SQL AVG

Using AVG on non-numeric columns leads to type errors.
Forgetting GROUP BY columns when aggregating per group.
Expecting NULLs to be treated as zero; they are skipped.
Misunderstanding DISTINCT: it removes duplicates before averaging, which can alter results.
Assuming AVG preserves integer type; many engines upcast to decimal or float.

First Introduced In

SQL-86 (ANSI SQL)

Frequently Asked Questions

Does AVG treat NULL as zero?

No. AVG skips NULL values. If every value evaluated is NULL, the function returns NULL, not zero.

How can I calculate a running average?

Use the window form: `AVG(column) OVER (ORDER BY date_column ROWS BETWEEN n PRECEDING AND CURRENT ROW)` to compute a moving window average.

When should I use DISTINCT with AVG?

Use `AVG(DISTINCT column)` when duplicates would distort the metric, such as averaging unique product prices instead of every recorded sale.

Why did AVG return a decimal instead of an integer?

Most engines upcast integers to decimal or floating types to retain precision. Cast the result back to an integer if truncation is acceptable.