AVG() Function SQL
The AVG() function is used to find the average of integer data in a column. Since this is an aggregate function, it returns only one line.
SELECT AVG(column) AS new_column_name
FROM table;
Note that the AS clause is not required. The query will return a result with the new column named AVG(column). In a game of Monopoly a player owns three pieces of property listed in the monopoly_prop table below. He wants to know the average price of his holdings.
| id | street_name | color | prop_price | house_price |
|---|---|---|---|---|
| 1 | Connecticut Ave | lt blue | 100 | 50 |
| 2 | St. James Place | orange | 180 | 100 |
| 3 | Board Walk | blue | 400 | 200 |
SELECT AVG(prop_price) AS avg_property_price
FROM monopoly_prop;
The result will be a single row in a single column in a table:
| avg_property_price |
|---|
| 226.6667 |
The AVG() function can also be used as an argument in a WHERE clause. For example:
SELECT street_name FROM monopoly_prop WHERE prop_price<(SELECT AVG(prop_price) FROM monopoly_prop);
This would return:
| id | street_name | color | prop_price | house_price |
|---|---|---|---|---|
| 1 | Connecticut Ave | lt blue | 100 | 50 |
| 2 | St. James Place | orange | 180 | 100 |
< SQL Functions | CASE Functions SQL >
