AVG()

AVG() Function in MySQL

Introduction
Average calculations provide essential statistical insights across datasets.
AVG() function computes arithmetic mean of non-NULL numeric values.
It is widely used for:

• Performance metrics
• Statistical analysis
• Business intelligence reports

What is AVG() Function
AVG() returns sum of non-NULL values divided by count of non-NULL values.
Automatically ignores NULL values in calculations.

Basic Syntax

sql

SELECT AVG(column_name) FROM table_name; 

Example

sql

SELECT AVG(marks) as average_marks FROM Students; 

Returns average of all non-NULL marks.

AVG with GROUP BY
Categorize averages.

sql

SELECT city, AVG(marks) as city_average 

FROM Students 

GROUP BY city; 

AVG with WHERE
Conditional averages.

sql

SELECT AVG(marks) FROM Students 

WHERE status = 'Active'; 

AVG with Multiple Aggregates
Combine statistics.

sql

SELECT city, 

       AVG(marks) as avg_marks, 

       COUNT(*) as student_count, 

       MIN(marks) as lowest, 

       MAX(marks) as highest 

FROM Students 

GROUP BY city; 

AVG with ORDER BY
Sort by averages.

sql

SELECT department, ROUND(AVG(marks), 2) as avg_marks 

FROM Students 

GROUP BY department 

ORDER BY avg_marks DESC; 

AVG(DISTINCT)
Average of unique values only.

sql

SELECT AVG(DISTINCT marks) as unique_avg 

FROM Students; 

AVG with JOIN
Cross-table averages.

sql

SELECT c.course_name, AVG(s.marks) as course_avg 

FROM Students s 

JOIN Courses c ON s.course_id = c.id 

GROUP BY c.id, c.course_name; 

AVG with HAVING
Filter grouped averages.

sql

SELECT city, AVG(marks) as avg_marks 

FROM Students 

GROUP BY city 

HAVING AVG(marks) > 75; 

Conditional AVG with CASE
Weighted or filtered averages.

sql

SELECT city,

       AVG(CASE WHEN status = 'Active' THEN marks ELSE NULL END) as active_avg,

       AVG(CASE WHEN marks > 0 THEN marks ELSE NULL END) as valid_avg

FROM Students

GROUP BY city;

AVG with Subquery
Compare against overall average.

sql

SELECT name, marks 

FROM Students 

WHERE marks > (SELECT AVG(marks) FROM Students WHERE marks IS NOT NULL); 

Handling Edge Cases
NULL and zero handling.

sql

SELECT

    COALESCE(AVG(marks), 0) as safe_avg,

    AVG(CASE WHEN marks > 0 THEN marks END) as positive_avg

FROM Students;

Execution Order with AVG
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Performance Considerations

• AVG(column) scans for NULLs
• AVG(DISTINCT) very slow (deduplication)
• Index GROUP BY columns
• Large tables: consider sampling

Important Notes

• Ignores NULL values automatically
• Empty set returns NULL (use COALESCE)
• Works only on numeric columns
• AVG(DISTINCT) removes duplicates first
• ROUND(AVG(), 2) for display formatting

Example Scenario
Complete course performance analysis:

sql

SELECT

    c.course_name,

    COUNT(s.id) as total_students,

    ROUND(AVG(s.marks), 2) as average_marks,

    COUNT(CASE WHEN s.marks >= 90 THEN 1 END) as a_grade_count,

    ROUND(AVG(CASE WHEN s.status = 'Active' THEN s.marks END), 2) as active_avg

FROM Courses c

LEFT JOIN Students s ON c.id = s.course_id

GROUP BY c.id, c.course_name

HAVING AVG(s.marks) > 70

ORDER BY average_marks DESC;

Common Mistakes

• AVG on non-numeric columns
• Surprised by NULL exclusion
• Performance hit from AVG(DISTINCT)
• WHERE vs HAVING confusion
• Forgetting COALESCE for NULL results

Key Points to Remember

• AVG computes mean of non-NULL values
• GROUP BY + AVG = category averages
• Automatically ignores NULLs
• AVG(DISTINCT) for unique value averages
• HAVING AVG() > X filters groups
• COALESCE(AVG(), 0) handles empty results