Skip to content

SQL AVG (Average) Function 📈

Prerequisites: Aggregate Functions Overview

Mentor's Note: The AVG function tells you the "Middle Point" or mean of your data. It's the most common tool for benchmarking, like "How is our store performing compared to the average?" 💡

🌟 The Scenario: The Cricket Match 🏏

Imagine you are calculating a player's batting average. - Total Runs: 500 - Matches: 10 - Average: 50 runs per match. 📈

💻 1. The Basic Syntax

SELECT AVG(column_name) FROM table_name;

Example: Average Salary

-- Scenario: benchmark pay scales
SELECT AVG(salary) AS "Average_Salary" FROM employees;

💻 2. Handling the "NULL Trap" ⚠️

This is a critical detail for exams. AVG ignores NULL rows.

Scenario: You have 4 students. - A: 80 marks - B: 60 marks - C: 40 marks - D: NULL (Absent)

Result: SQL calculates (80 + 60 + 40) / 3 = 60. It skips Student D entirely. If you wanted to treat Student D as 0, you must use COALESCE or NVL:

-- Treats NULL as 0 before averaging
SELECT AVG(COALESCE(marks, 0)) FROM students;
-- Result: (80+60+40+0) / 4 = 45

🎨 Visual Logic: The Balance

graph LR
    A[Row 1] --> B[+]
    C[Row 2] --> B
    D[Row 3] --> B
    B --> E[/ Total Rows]
    E --> F[Average Result]

📈 Learning Path