w3resource logo

>Sql avg() function

SQL avg() function


Seondary Nav


The SQL AVG function calculates the average value of a column of numeric type. It returns the average of all non NULL values


AVG ([ALL | DISTINCT] expression ) 

MySQL, PostgreSQL, and SQL Server

All the above platforms support the SQL syntax of AVG.

DB2 and Oracle Syntax

AVG ([ALL | DISTINCT] expression ) OVER (window_clause) 


Name Description
ALL Applies to all values.
DISTINCT Return the sum of unique values.
expression Expression made up of a single constant, variable, scalar function, or column name. Expression is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted.

In the subsequent pages we have discussed how to apply AVG() with various SQL clauses. For those applications, we have used Oracle 10g Express Edition.


Sample table : orders

To get the data the average of 'advance_amount' form the 'orders' table, the following SQL statement can be used :

SELECT AVG( advance_amount )
FROM orders;



SQL avg() with null

Sample table : customer

To get the data of 'agent_code', sum of 'opening_amt', number of customer for each agent, and 'receive_amt' from the 'customer' table, the following SQL statement can be used :

SELECT agent_code, SUM (opening_amt), 
COUNT(*), ROUND(SUM (opening_amt) /COUNT(*)) AS MYAVG, 
ROUND(AVG (opening_amt)) AS SQLAVG 
FROM customer 
GROUP BY agent_code;


---------- ---------------- ---------- ---------- ----------
A002                  22000          3       7333       7333
A004                  25000          3       8333       8333
A007                  16000          2       8000       8000
A009                   6000          1       6000       6000
A011                   5000          1       5000       5000
A012                   5000          1       5000       5000
A010                  22000          3       7333       7333
A001                   8000          1       8000       8000
A008                  13000          3       4333       4333
A006                   8000          2       4000       4000
A005                  19000          3       6333       6333
A003                  12000          2       6000       6000

SQL avg() with where

Sample table : orders

To get the average of 'advance_amount' from the 'orders' table with following conditions -

1. 'ord_amount' must be more than 1000,

2. and 'ord_amount' must be up to 4500,

the following SQL statement can be used :

SELECT AVG( advance_amount) 
FROM orders 
WHERE ord_amount>1000 AND ord_amount <= 4500;



SQL avg() with sum()

In the following we are going to discuss the usage of SQL SUM() along with the SQL AVG() function.


Sample table : orders

To get the sum of 'advance_amount' and average of 'advance_amount' from the 'orders' table, the following SQL statement can be used :

SELECT SUM(advance_amount), AVG(advance_amount)
FROM orders;


------------------- -------------------
              22650          629.166667

All Aggregate Functions

SQL Aggregate Functions, slide presentation

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.


Is this content useful for you?

Looking for some other tutorial?