SQL AVG() function

AVG() function

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

Syntax:

AVG ([ALL | DISTINCT] expression ) 

DBMS Support: COUNT() function

DB2 and Oracle Syntax:

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

Parameters:

Syntax diagram - AVERAGE() function

Example:

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

SELECT AVG( advance_amount )
FROM orders;

Sample table: orders

Output:

AVG(ADVANCE_AMOUNT)
-------------------
         629.166667

Pictorial Presentation:

SQL AVG() with null

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;

Sample table : customer

Output:

AGENT_CODE SUM(OPENING_AMT)   COUNT(*)      MYAVG     SQLAVG
---------- ---------------- ---------- ---------- ----------
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

To get the average of 'advance_amount' from the 'orders' table with the 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;

Sample table : orders

Relational Algebra Expression:

Relational Algebra Tree:

Output:

AVG(ADVANCE_AMOUNT)
-------------------
                800

Pictorial Presentation:

SQL AVG() with SUM()

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;

Sample table : orders

Relational Algebra Expression:

Relational Algebra Tree:

Output:

SUM(ADVANCE_AMOUNT) AVG(ADVANCE_AMOUNT)
------------------- -------------------
              22650          629.166667

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition

Here is a slide presentation of all aggregate functions.

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: SUM and COUNT Using Variable and inner join
Next: Avg Decimal Places Using Cast within and outside avg