Average in SQL - sql - sql tutorial - learn sql

- SQL AVG function is used to find out the average of a field in various records.
- To find the average value in an expression using the AVG function.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialSyntax:
Syntax:
- The syntax for the AVG function is,
SELECT AVG (<expression>)
FROM "table_name";
can be a column name or an arithmetic operation. - An arithmetic operation can include more than one column, such as ("column1" * "column2").
- It is also possible to have one or more columns in addition to the AVG function in the SELECT statement.
- In those cases, these columns need to be part of the GROUP BY clause as well:
SELECT "column_name1", "column_name2", ... "column_nameN", AVG (<expression>)
FROM "table_name";
GROUP BY "column_name1", "column_name2", ... "column_nameN";
Examples:
- We use the following table for our examples.
Table Store_Information
Store_Name | Sales | Txn_Date |
---|---|---|
Los Angeles | 1500 | Jan-05-1999 |
San Diego | 250 | Jan-07-1999 |
Los Angeles | 300 | Jan-08-1999 |
Boston | 700 | Jan-08-1999 |
Example 1: AVG function on a column
- To find the average sales amount, we type in,
SELECT AVG(Sales) FROM Store_Information;
Result:
AVG(Sales) |
---|
687.5 |
- 687.5 represents the average value of all Sales entries: (1500+250+300+700) / 4.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExample 2: AVG function on an arithmetic operation
Example 2: AVG function on an arithmetic operation
- Assume that sales tax is 10% of the sales amount, we use the following SQL statement to get the average sales tax amount:
SELECT AVG(Sales*0.1) FROM Store_Information;
Result:
AVG(Sales*0.1) |
---|
68.75 |
- SQL will first calculate "Sales*0.1" and then apply the AVG function to the result for the final answer.
Example 3: AVG function with a GROUP BY clause
- To get the average amount of sales for each store, we type in,
SELECT Store_Name, AVG(Sales) FROM Store_Information GROUP BY Store_Name;
Result:
Store_Name | MIN(Sales) |
---|---|
Los Angeles | 900 |
San Diego | 250 |
Boston | 700 |
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialSQL AVG Sample Application
SQL AVG Sample Application
