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.

idstreet_namecolorprop_pricehouse_price
1Connecticut Avelt blue10050
2St. James Placeorange180100
3Board Walkblue400200
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:

idstreet_namecolorprop_pricehouse_price
1Connecticut Avelt blue10050
2St. James Placeorange180100

< SQL Functions | CASE Functions SQL >


Send notes in disappearing ink!

Interesting Pages