Definition and usage
The AVG function returns the average of a numeric column. NULL values are not included in the calculation.
SQL AVG () syntax
SELECT AVG (column_name) from table_name
SQL AVG () instance
We have the following "Orders" table:
o_id |
OrderDate |
Orderprice |
Customer |
1 |
2008/12/29 |
1000 |
Bush |
2 |
2008/11/23 |
1600 |
Carter |
3 |
2008/10/05 |
700 |
Bush |
4 |
2008/09/28 |
300 |
Bush |
5 |
2008/08/06 |
2000 |
Adams |
6 |
2008/07/21 |
100 |
Carter |
Example 1
Now, we want to calculate the average of the "Orderprice" field.
We use the following SQL statement:
SELECT AVG (Orderprice) as Orderaverage from Orders
The result set looks like this:
Example 2
Now, we want to find customers with Orderprice values above the Orderprice average.
We use the following SQL statement:
Select Customer from Orderswhere orderprice> (select AVG (orderprice) from Orders)
The result set looks like this:
Customer |
Bush |
Carter |
Adams |
SQL AVG function