How to combine the AVG(). function with the WHERE clause in SQL

Overview

The AVG() function is one of the aggregate functions that can be used in conjunction with the WHERE clause to gain more insights from our data.

In SQL, the AVG() function is used to compute the average of numeric values in a column.

Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Example

Let’s assume we have a Person table with the columns—name, salary, state, and gender.

We want to get the average salary for a person living in Lagos.

The following code shows how to use the AVG() function with the WHERE clause in SQL:

CREATE TABLE Person (
    ID int,
    name varchar(100),
    salary int,
    gender varchar(10),
    state varchar(15)
);
-- Insert data
INSERT INTO Person
VALUES (1,'Sharon Peller',40000,'Female','Kogi');
INSERT INTO Person
VALUES (2,'Paul Dons',150000,'Male','Lagos');
INSERT INTO Person
VALUES (3,'Ameera Abedayo',200000,'Female','Imo');
INSERT INTO Person
VALUES (4,'Maria Elijah',320000,'Female','Lagos');
INSERT INTO Person
VALUES (5,'David Hassan',250000,'Male','Abuja');
INSERT INTO Person
VALUES (6,'Niniola Disu',80000,'Female','Lagos');
INSERT INTO Person
VALUES (7,'Praise Dominion',340000,'Female','Lagos');
INSERT INTO Person
VALUES (7,'Divine Favour',280000,'Female','Abuja');
INSERT INTO Person
VALUES (7,'Praise Dominion',100000,'Female','Lagos');
INSERT INTO Person
VALUES (8,'Joe Smith',75000,'Lagos');
-- Query 
SELECT AVG(salary)
FROM Person
WHERE state = 'Lagos';

Explanation

  • Line 1–7: We create a table called Person with the columns: id, name, salary, gender, and state.
  • Line 10–29: We insert data into the Person table.
  • Line 32–34: We use the AVG() function with the WHERE() clause to get the average salary of a person living in Lagos.
New on Educative
Learn to Code
Learn any Language as a beginner
Develop a human edge in an AI powered world and learn to code with AI from our beginner friendly catalog
🏆 Leaderboard
Daily Coding Challenge
Solve a new coding challenge every day and climb the leaderboard

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)