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.

Free Resources

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