w3resource logo


>Sql avg() function

SQL avg() function

rating Average rating 6 out of 10. Total 10 users rated.

<<PreviousNext>>

Description

The 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 )

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)

Parameters

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.

Example

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;

Output

Sql Avg() with where

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;

Output

Sql Avg() with null

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;

Output

Sql Avg() with where

SQL avg() with sum()

Description

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

Example

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;

Output

Sql Avg() with sum()

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

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.



<<PreviousNext>>