How to filter records with an aggregate function AVG in SQL

Problem

You have a table with multiple records and you want to filter these records based on the average (AVG) of a specific column. In other words, you want to display only those records where the value of a specific column is higher than the average value of that column in the entire table.

Sample Data

Let's consider a table named Orders that contains the following data:

MySQL Solution

SELECT *
FROM Orders
WHERE Amount > (SELECT AVG(Amount) FROM Orders);

PostgreSQL Solution

SELECT *
FROM Orders
WHERE Amount > (SELECT AVG(Amount) FROM Orders);

Explanation

In both MySQL and PostgreSQL, the SQL query works the same.

The inner query (SELECT AVG(Amount) FROM Orders) calculates the average Amount from the Orders table. The outer query then uses this average value to filter the records from the Orders table where the Amount is greater than this average.

This SQL statement will return all the records from the 'Orders' table where the 'Amount' is higher than the average 'Amount' in the entire table. So, in this case, it will return the records where the 'Amount' is 400 and 500 since the average (300) is less than these values.