SQL AVG Function – Syntax and Examples

SQL AVG Function

The SQL AVG function is used to calculate the average value of a numeric column. It adds up all the values in the specified column and then divides by the count of non-NULL values, returning the average.

The AVG function is commonly used with the GROUP BY clause to find the average value for each group in the dataset.

In this tutorial, we will go through SQL AVG Function, its syntax, and how to use this function in SQL statements, with the help of well detailed examples.

---

Syntax of SQL AVG Function

The basic syntax of the SQL AVG function is as follows:

SELECT AVG(column_name) AS avg_value
FROM table_name
[WHERE condition];

Each part of this syntax has a specific purpose:

• SELECT: Specifies the column to retrieve the average value from.
• AVG(column_name): The AVG function, which returns the average of all non-NULL values in the specified column.
• AS avg_value: An alias for the calculated average value in the results.
• FROM: Specifies the table from which to retrieve data.
• WHERE (optional): Filters the data based on specified conditions before applying the AVG function.

---

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the AVG function in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality along with a grades table that records students’ scores.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a students table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Create a grades table:

CREATE TABLE grades (
    student_id INT,
    subject VARCHAR(50),
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

5. Insert sample data into the students and grades tables:

INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

INSERT INTO grades (student_id, subject, score)
VALUES
(1, 'Math', 85),
(2, 'Math', 78),
(3, 'Science', 90),
(4, 'Math', 88),
(5, 'Science', 70);

---

Examples: Using AVG Function in Queries

Now, let’s explore different scenarios of using the AVG function with these tables.

---

1. Finding the Average Score Across All Subjects

To find the average score in the grades table:

SELECT AVG(score) AS avg_score
FROM grades;

This query returns the average of all scores across subjects in the grades table.

---

2. Finding the Average Age of Students

To find the average age of students in the students table:

SELECT AVG(age) AS avg_age
FROM students;

This query returns the average age of all students in the students table.

---

3. Using AVG with a WHERE Clause

To find the average score for the subject “Math” only:

SELECT AVG(score) AS avg_math_score
FROM grades
WHERE subject = 'Math';

This query finds the average score for “Math” in the grades table, filtering scores only for that subject.

Reference: SQL WHERE Clause

---

4. Using AVG with GROUP BY to Find Average Scores per Subject

To find the average score for each subject:

SELECT subject, AVG(score) AS avg_score
FROM grades
GROUP BY subject;

This query returns the average score for each subject in the grades table, displaying the average score per subject.

Reference: SQL GROUP BY

---

FAQs for SQL AVG

1. What does the SQL AVG function do?

The AVG function calculates the average of a column by summing all values and dividing by the count of non-NULL values.

2. Can AVG be used with non-numeric data?

No, AVG is designed for numeric data. It does not work with text or date data types.

3. How do I find the average value for each group?

Use the GROUP BY clause with AVG to find the average value for each group in the dataset.

4. Can AVG be used with WHERE conditions?

Yes, you can filter rows before calculating the average by using a WHERE clause with the AVG function.

5. Does AVG consider NULL values?

No, AVG ignores NULL values in the column and only calculates the average of non-NULL values.