Average is a statistical operation that adds a list of values and gives output by dividing it with count of total values. MySQL Average is the same operation that allows you to find average value for data columns in MySQL data tables. The average is calculated for numeric columns only.
Syntax of MySQL Average Function
The average function is used in a SELECT query according to the given syntax.
- AVG is the keyword used for MySQL Average operation.
- DISTINCT is keyword. It is optional. When this keyword is used in the query only distinct values from column or expression are used to calculate average value.
- table_column is the column name of the table whose average is to be calculated.
- Expression is the expression creating table column names and MySQL Arithmetic Operators. For example salary is a calculated value. It can be calculated with columns like basic pay, allowances and deductions. So, the expression that is used to calculate total salary of employees can be used in average function.
Examples of MySQL Average
Average function used with Non-Numeric values
In this example the AVG function is used with artist_name which is a string data type column. Since AVG function requires only numeric data, 0 is returned by this query.
SELECT AVG(artist_name) FROM artists;
Average function used with Numeric values
In this example the average of column age is calculated. The second query uses the expression with column name and arithmetic operator.
SELECT AVG(age) FROM artists;
SELECT AVG(age*10) FROM artists;
Average function used with distinct Numeric values
This query finds average of distinct age values by using DISTINCT keyword in MySQL Average function.
SELECT AVG(DISTINCT age) FROM artists;
Average function with non-aggregated column
AVG aggregate function is used twice with two columns and with a column used without an aggregate function. country value of first row of the table is displayed
SELECT country, AVG(ratings), AVG(DISTINCT age) FROM artists;
Points to remember
- You can use more than one average functions for different column names or expressions.
- Average function can be used only in a SELECT statement.
- To include only unique values in calculating average use DISTINCT keyword within parenthesis before column name or expression.
- When aggregate function like average is used in a SELECT statement the column names or expressions without aggregate functions can be given. Non-aggregated values from the first row of the table is displayed along with the aggregate function values.