SQL avg() function
has average rating
9
out of 10.
Total 4 users rated.
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 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 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 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
Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.
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.
