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:

</>
Copy
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:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a students table:

</>
Copy
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:

</>
Copy
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:

</>
Copy
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);