MySQL AVG function

This function works on numerical data type values. The average function shows the average value of the data set. If an average function returns a null value, then the row is not available in the table. The average value supports the arithmetic operation of the column values. The average function returns integer and non-integer values.

Syntax

The AVG () function syntax executes below.

AVG(function_expression)

The following syntax shows MySQL AVG () function with table data.

SELECT AVG(function_expression) FROM Table_Name;

The given syntax shows MySQL AVG () function with clause and condition.

SELECT AVG(function_expression) FROM Table_Name WHERE condition;

Description

• The AVG() function returns average values of the table data or column data.
• The "function_expression" represents the value or column of the table for function operation.
• The "WHERE" clause is used to filter the records of the table.

Prerequisite

• Create a table with the below statement.

mysql> create table crafts (  

    id int NOT NULL,

    product_name varchar(100),    

    quantity int,  

    date date

    );  

• Insert the data in the table as per data type.

mysql> insert into crafts(id,

product_name, quantity,  

date) 

VALUES           

(1, 'pen', 80, '2021-08-18'),  

(2, 'sketch book', 65, '2021-07-11'),  

(3, 'stickers', 84, '2021-01-22'),  

(4, 'colors', 55, '2021-02-21'),  

(5, 'eraser', 96, '2021-06-22'),  

(6, 'sketch pencil', 98, '2021-01-15');

• You can refer to the table structure and information using the output query.

mysql> SELECT * FROM crafts;

Examples of the AVG function

1) Example: This example explains the basic AVG function. It takes a single column which is the "quantity" column of the table, and then finds its average value.

mysql> SELECT AVG(quantity ) AS quantity FROM crafts;

 OUTPUT    

The average function returns the average value of the quantity column as output. The quantity column has approximately 80 average values, but the table shows floating values.

2) Example: This example uses the "DISTINCT" clause with the AVG function. This clause removes duplicate data and then displays the average values of these data.

mysql> SELECT AVG( DISTINCT quantity ) AS quantity FROM crafts;

 Output

The AVG() function contains a quality column with the DISTINCT keyword. This keyword removes similar values in the column and returns an average value.

3) Example: The AVG function using the date value example shows below. This function returns the date value into an integer value. The example returns the average value of the entire date column.

mysql> SELECT AVG(date) AS date FROM crafts;

OUTPUT

The average function supports the date and floating values of the table. The date converts into a number and returns the average date of the crafts table. You can see the date returns into floating value.

4) Example: The AVG function with the "DISTINCT" and the "WHERE" clauses example shows below. This example uses the average function on the quantity column. It returns the average "quantity" that has greater than 65 values in the table.

mysql> SELECT AVG(DISTINCT quantity ) AS quantity 
FROM crafts 
WHERE quantity  > 65;

 OUTPUT

5) Example: The AVG function using multiple columns example shows below. This function operates on the "id", "date", and "quantity" columns of the crafts table and returns the average value of all columns.

mysql> SELECT AVG(id) AS id, 
AVG(date) AS date, 
AVG(quantity ) AS quantity 
FROM crafts;

OUTPUT

The average function uses with the three columns of the crafts table. Each column required the "AVG()" method separately.

6) Example: The AVG function with the "WHERE" clause and "AND" operator example shows below. This function can operate arithmetic operation on several columns simultaneously. The "where" clause uses "greater than" and "less than" conditions.  The "AND" operator works on the "quantity" and "id" columns.

mysql> SELECT AVG(id) AS id, 
AVG(date) AS date, 
AVG(quantity ) AS quantity 
FROM crafts 
WHERE quantity  > 80 AND id < 4;

Output

The output image shows one row of the average values of the table.

7) Example: The AVG function with the "GROUP BY" clause example shows below. This average function example uses the "GROUP BY" clause on the "quantity" column.

mysql> SELECT AVG(id) AS id, 
AVG(date) AS date, 
AVG(quantity ) AS quantity 
FROM crafts 
GROUP BY quantity;

OUTPUT

This output display entire rows of the given table because of the "GROUP BY" clause. The id, date, and quantity columns display values with floating-point.

8) Example: The AVG function with the "WHERE" clause and "OR" operator example shows below. Here the WHERE clause applies two conditions using the "OR" logical operator.

mysql> SELECT AVG(id) AS id, 
AVG(date) AS date, 
AVG(quantity ) AS quantity 
FROM crafts 	
WHERE quantity > 80 OR id < 3;

OUTPUT

Here, the image shows the single row of the average values using the "WHERE" condition. The quantity column applies to the "greater than" 80 value. The "id" column applies to the "less than" three values.

9) Example: The AVG function with the "GROUP BY" and "HAVING" clause example shows below. In this example, the "quantity" column modifies with the "GROUP BY" clause and "HAVING" condition. The "HAVING" clause applies the "greater than" condition.

mysql> SELECT AVG(id) AS id, 
AVG(date) AS date, 
AVG(quantity ) AS quantity 
FROM crafts 
GROUP BY quantity 
HAVING quantity > 85;

OUTPUT

The output image shows two rows of the given columns. The table displays data greater than the "85" quantity of the crafts table.

10) Example: The AVG function with the multiple clauses example shows below.

The average function can be used in multiple columns of the table. The output returns particular table columns and rows.

mysql> SELECT AVG(id) AS id, 
AVG(date) AS date, 
AVG(quantity ) AS quantity 
FROM crafts 
WHERE id > 2 
GROUP BY quantity 
HAVING quantity > 85 
ORDER BY quantity ASC;

OUTPUT

The average function shows output based on the WHERE and HAVING clauses. The WHERE clause uses the "greater than two rows" on the "id" column. The "HAVING" clause uses the "greater than 85 quantity" on the "quantity" column. The output displays two rows and three columns of the table data.