SQL Average (AVG) Tutorial
The AVG command is used to get the average value of a column. It is frequently used with the group by command.
Example 1 (no aggregation)
-- Works in PostgreSQL, MySQL, SQL Server, and Oracle
SELECT
AVG(dollar_amount) as avg_rent_payment
FROM
rent_payments;
rent_payments table
| id | tenant_id | payment_date | dollar_amount |
|---|---|---|---|
| 1 | 1 | 3/1/2018 | 2000 |
| 2 | 2 | 3/2/2018 | 1500 |
| 3 | 3 | 3/1/2018 | 1800 |
| 4 | 4 | 3/3/2018 | 1900 |
| 5 | 1 | 4/2/2018 | 2000 |
| 6 | 2 | 4/2/2018 | 1500 |
| 7 | 3 | 4/1/2018 | 1800 |
| 8 | 4 | 4/2/2018 | 1900 |
Query results
| avg_rent_payment |
|---|
| 1800 |
Example 2 (with aggregation)
-- Works in PostgreSQL, MySQL, SQL Server, and Oracle
SELECT
payment_date,
avg(dollar_amount) as avg_rent_payment
FROM
rent_payments
GROUP BY
payment_date;
rent_payments table
| id | tenant_id | payment_date | dollar_amount |
|---|---|---|---|
| 1 | 1 | 3/1/2018 | 2000 |
| 2 | 2 | 3/2/2018 | 1500 |
| 3 | 3 | 3/1/2018 | 1800 |
| 4 | 4 | 3/3/2018 | 1900 |
| 5 | 1 | 4/2/2018 | 2000 |
| 6 | 2 | 4/2/2018 | 1500 |
| 7 | 3 | 4/1/2018 | 1800 |
| 8 | 4 | 4/2/2018 | 1900 |
Query results
| payment_date | avg_rent_payment |
|---|---|
| 3/1/2018 | 1900 |
| 3/2/2018 | 1500 |
| 3/3/2018 | 1900 |
| 4/2/2018 | 1800 |
| 4/1/2018 | 1800 |
Explanation
For each distinct value in the payment_date column, we are adding up the dollar_amount cells, and dividing by the total amount of rows with that particular payment_date.