AVG Function

Overview

The AVG function computes the average value of a numeric column or partition.

Learning Objectives

• Calculate average bounds with AVG().
• Grasp how AVG handles NULL values under the hood.
• Perform group averages and round the results.

Detailed Concept Explanation

AVG calculates the average of a numeric column by dividing the sum of the values by the count of values.

The Null Count Trap

Because AVG ignores NULL values, the denominator in its average calculation only counts rows containing non-null values.

For example, if you have 4 salaries: 100, 200, 300, and NULL: AVG is calculated as: (100 + 200 + 300) / 3 (yielding 200). If you want to treat the NULL value as 0 and divide by 4, you must explicitly coalesce the values before averaging: AVG(COALESCE(salary, 0)) (yielding 150).

Code Examples

SQL

-- Compare standard AVG against NULL-coalesced AVG
SELECT 
  AVG(bonus) AS average_bonus_paid, -- Divides only by employees who received bonuses
  AVG(COALESCE(bonus, 0)) AS average_bonus_overall -- Treats missing bonuses as $0
FROM payroll;

Best Practices

• Numeric Casting: If the column is an integer type, cast it to a float or multiply it by 1.0 before averaging in databases that perform integer division, to prevent the average from truncating decimals.

Interview Perspective

Interactive Challenges

Summary

AVG calculates averages while skipping nulls. Make sure to coalesce null values to 0 if they need to be included in the average.