AVG function in SQL - Deep Dive

Overview - AVG function

What is it?

The AVG function in SQL calculates the average value of a numeric column in a table. It adds up all the numbers in that column and then divides by the count of those numbers. This helps find the typical or central value in a set of data. It works only with numbers and ignores empty or missing values.

Why it matters

Without the AVG function, finding the average of many numbers stored in a database would require manual calculation or complex code. This function saves time and reduces errors, making it easy to summarize data like average sales, temperatures, or test scores. It helps businesses and people make decisions based on typical values rather than individual data points.

Where it fits

Before learning AVG, you should understand basic SQL SELECT queries and how to work with tables and columns. After mastering AVG, you can learn other aggregate functions like SUM, COUNT, MIN, and MAX, and how to group data using GROUP BY for more detailed summaries.

Mental Model

Core Idea

AVG finds the middle value by adding all numbers and dividing by how many there are.

Think of it like...

Imagine you have a basket of apples with different weights. To find the average weight, you weigh all apples together and then divide by the number of apples. AVG does the same with numbers in a database column.

┌───────────────┐
│ Numeric Data  │
│ 10, 20, 30, 40│
└──────┬────────┘
       │
       ▼
┌─────────────────────────┐
│ AVG = (10+20+30+40) / 4 │
│        = 100 / 4 = 25   │
└─────────────────────────┘

Build-Up - 7 Steps

1

Foundation

What AVG Function Does

Concept: AVG calculates the average of numbers in a column.

AVG adds all the numbers in a column and divides by the count of those numbers. It ignores NULL or missing values automatically. For example, if a column has 10, 20, 30, AVG returns (10+20+30)/3 = 20.

Result

You get a single number representing the average of the column's values.

Understanding AVG as a simple sum divided by count helps you grasp how it summarizes data quickly.

2

Foundation

Using AVG in a Basic Query

Concept: How to write a SQL query using AVG.

To find the average price from a products table, you write: SELECT AVG(price) FROM products; This returns one number: the average price of all products.

Result

The query returns the average value of the price column.

Knowing the syntax lets you quickly get average values without extra calculations.

3

Intermediate

AVG Ignores NULL Values

🤔Before reading on: Do you think AVG counts NULL values as zero or ignores them? Commit to your answer.

Concept: AVG does not count NULLs when calculating the average.

If a column has values 10, NULL, 20, AVG adds only 10 and 20, then divides by 2, not 3. So AVG returns (10+20)/2 = 15, ignoring the NULL.

Result

The average excludes missing data, giving a true average of existing numbers.

Knowing NULLs are ignored prevents mistakes when interpreting averages with missing data.

4

Intermediate

Combining AVG with GROUP BY

🤔Before reading on: Will AVG calculate one average for the whole table or separate averages per group? Commit to your answer.

Concept: AVG can calculate averages for groups of rows using GROUP BY.

If you want average sales per region: SELECT region, AVG(sales) FROM sales_data GROUP BY region; This returns average sales for each region separately.

Result

You get multiple averages, one per group defined by GROUP BY.

Grouping data before averaging lets you compare averages across categories easily.

5

Intermediate

AVG with Filtering Using WHERE

Concept: You can filter rows before averaging using WHERE clause.

To find average sales only for 2023: SELECT AVG(sales) FROM sales_data WHERE year = 2023; This averages sales from rows where year is 2023 only.

Result

The average reflects only the filtered subset of data.

Filtering before averaging lets you focus on specific data slices for more relevant insights.

6

Advanced

AVG on Expressions and Calculated Columns

🤔Before reading on: Can AVG work on calculations like price * quantity or only on raw columns? Commit to your answer.

Concept: AVG can calculate averages on expressions, not just columns.

You can average results of calculations: SELECT AVG(price * quantity) FROM orders; This averages the total price per order line, not just price or quantity alone.

Result

You get the average of the calculated values, enabling flexible summaries.

Using expressions inside AVG expands its power to summarize complex data.

7

Expert

AVG and Data Types Impact on Results

🤔Before reading on: Does AVG always return the same data type as the input column? Commit to your answer.

Concept: AVG's return type depends on input data types and can affect precision.

If AVG is used on integers, some SQL systems return a decimal or float to avoid losing fractions. But in some cases, it may truncate decimals if not careful. Understanding how your database handles this prevents rounding errors.

Result

You get accurate averages with correct data types, avoiding surprises in results.

Knowing AVG's data type behavior helps ensure precise calculations and prevents subtle bugs.

Under the Hood

AVG works by scanning the selected rows, summing all non-NULL numeric values, and counting how many values were summed. Then it divides the sum by the count. Internally, the database engine optimizes this by streaming data and using efficient aggregation algorithms, sometimes using indexes or parallel processing for large datasets.

Why designed this way?

AVG was designed as a simple aggregate function to provide a quick summary of numeric data. Ignoring NULLs avoids skewing results with missing data. The division approach is intuitive and mathematically sound. Alternatives like median or mode are more complex and less efficient, so AVG is a fast, general-purpose summary.

┌───────────────┐
│ Input Rows    │
│ (with numbers)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Sum Non-NULL   │
│ Values        │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Count Non-NULL │
│ Values        │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Divide Sum by │
│ Count         │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Return AVG    │
│ Value         │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does AVG count NULL values as zero or ignore them? Commit to your answer.

Common Belief: AVG treats NULL values as zero when calculating the average.

Reality: AVG completely ignores NULL values and does not count them in the total or divisor.

Why it matters: If you think NULLs are zeros, you might underestimate the average, leading to wrong conclusions about data quality or performance.

Quick: Does AVG always return an integer if input is integer? Commit to your answer.

Common Belief: AVG returns the same data type as the input column, so integer input means integer output.

Reality: Most SQL systems return a decimal or float for AVG, even if input is integer, to preserve fractional averages.

Why it matters: Assuming integer output can cause bugs when decimals are truncated or rounded unexpectedly.

Quick: Does AVG calculate the average of all rows regardless of grouping? Commit to your answer.

Common Belief: AVG always calculates one average for the entire table, ignoring groups.

Reality: When used with GROUP BY, AVG calculates separate averages per group.

Why it matters: Misunderstanding grouping leads to wrong data summaries and misinterpretation of grouped reports.

Quick: Can AVG be used on non-numeric columns like text? Commit to your answer.

Common Belief: AVG can be used on any column, including text or dates.

Reality: AVG only works on numeric data types; using it on non-numeric columns causes errors.

Why it matters: Trying to average non-numeric data wastes time and causes query failures.

Expert Zone

1

AVG's behavior with floating-point numbers can introduce tiny rounding errors due to how computers store decimals.

2

Some databases optimize AVG by combining SUM and COUNT operations in a single pass for performance on large datasets.

3

When AVG is used with DISTINCT, it averages only unique values, which can drastically change results.

When NOT to use

AVG is not suitable when you need the middle value (median) or the most frequent value (mode). For skewed data, median is better. Also, avoid AVG on categorical or non-numeric data; use COUNT or other functions instead.

Production Patterns

In real systems, AVG is often combined with GROUP BY and HAVING clauses to filter groups by average thresholds. It's also used in dashboards to show KPIs like average revenue or average response time. Indexes on numeric columns can speed up AVG calculations on large tables.

Connections

Median

Related aggregate function that finds the middle value instead of the mean.

Knowing AVG helps understand median as another way to summarize data, especially when data is skewed.

MapReduce (Big Data)

AVG is implemented as a reduce operation that combines sums and counts from map tasks.

Understanding AVG's sum and count aggregation helps grasp how distributed systems compute averages efficiently.

Arithmetic Mean in Statistics

AVG is the database equivalent of the arithmetic mean concept in statistics.

Knowing AVG connects database queries to fundamental statistical measures used in data analysis.

Common Pitfalls

#1 Including NULL values in the average calculation incorrectly.

Wrong approach:

SELECT SUM(price) / COUNT(*) FROM products;

Correct approach:

SELECT AVG(price) FROM products;

Root cause:

Using COUNT(*) counts all rows including those with NULLs, skewing the average calculation.

#2 Using AVG on non-numeric columns causing errors.

Wrong approach:

SELECT AVG(product_name) FROM products;

Correct approach:

SELECT AVG(price) FROM products;

Root cause:

AVG requires numeric data; applying it to text columns is invalid.

#3 Assuming AVG returns integer when input is integer, losing decimals.

Wrong approach:

SELECT CAST(AVG(quantity) AS INT) FROM orders;

Correct approach:

SELECT AVG(quantity) FROM orders;

Root cause:

Casting AVG result to integer truncates decimals, losing precision.

Key Takeaways

The AVG function calculates the average by summing numeric values and dividing by their count, ignoring NULLs.

AVG works only on numeric columns and returns a decimal or float to preserve precision.

Using AVG with GROUP BY lets you find averages per category or group, enabling detailed data summaries.

Filtering data with WHERE before AVG focuses the average on relevant subsets.

Understanding AVG's behavior with NULLs and data types prevents common mistakes and ensures accurate results.