[Basic Grammar] SQL AVG() Function

The AVG() function returns the average value of a numeric column.

 

• Syntax 

SELECT AVG(column_name)
FROM table_name
WHERE condition;

 

Example 

Find the average price of all cups. 

SELECT AVG(Price)
FROM Cups;

** NULL is ignored**

 

• Add a WHERE Clause 

You can specify conditions by using WHERE clause. 

 

Example 

Return the average price of sweaters in category 90:

SELECT AVG(Price) 
FROM Sweaters
WHERE CustomerID = 90;

 

• Higher Than Average 

If you want to list records with a higher price than average, you can use the AVG() function in a sub query:

Example 

Return all the cups with a higher price than the average price:

SELECT * FROM Cups
WHERE price > (SELECT AVG (Price) FROM Cups);

 

Exercises 

1. Write an SQL query to retrieve all employees with a salary higher than the average salary in the "Employees" table.

SELECT * FROM Employees
WHERE salary > SELECT(AVG(salary) FROM Employees);

2. Select all orders with a total amount less than the average total amount in the "Orders" table.

SELECT * FROM Orders 
WHERE TotalAmount < SELECT (AVG(total amount) FROM Orders);