How To Use AVG Function In MySQL

Introduction

In this article, we'll explore the AVG() function in MySQL, including how to use it to find the average value of a column in a table with examples.

What is MySQL?

MySQL is an acronym for My Structured Query Language and a database management system. In this MySQL, SQL is a programming language used to work with data in relational databases. SQL is the most common standardized language used to access databases. For more details on MySQL, read the full article here- Introduction To MySQL.

What is the Aggregate() Function?

The Aggregate function performs a calculation on multiple values and returns a single value. Except for COUNT(*), aggregate functions ignore null values.

There are some functions that operate on sets of values.

COUNT() Counts how many rows are in a particular column.
SUM() Adds together all the values in a particular column.
MIN() and MAX() Returns the lowest and highest values in a particular column, respectively.
AVG() Calculates the average of a group of selected values.

For a detailed description of the Aggregate() function, read this article- How To Use Aggregate Functions In MySQL.

What is AVG() function?

AVG() is a SQL Aggregate function that calculates the average of a selected group of values. The AVG() function returns the average value of a numeric column. It's beneficial but has some limitations. First, it can only be used on numerical columns. Second, it ignores nulls completely.

Importance of using AVG() function

AVG function is commonly used in data analysis and reporting. Here are some reasons why aggregate functions are important.

Summarizing Data- Aggregate functions allow us to summarize large amounts of data quickly and easily. For example, we can use the SUM() function to calculate the total revenue or sales for a specific period.
Data Analysis- Aggregate functions are essential for data analysis, as they allow us to perform calculations on data and derive insights from it. For instance, we can use the AVG() function to calculate the average salary of employees in a company.
Reporting- Aggregate functions are often used in generating reports. They help in summarizing and presenting data in a meaningful way, making it easier for users to understand and interpret the data.
Efficient Querying- By using aggregate functions, we can avoid writing lengthy and complex queries. This makes the querying process more efficient and less prone to errors.

Syntax

SELECT AVG(Column_name) FROM <table_name>;

Example

Creating a database

//creating a database in MySQL with the name Fruitsdb
create database FruitsDb;
//using database 
use FruitsDb;

In this step, we create a new database called "FruitsDb". The "create database" statement is used to create a new database, and the "use" statement is used to switch to the newly created database.

Creating a table

create table Fruits
(FruitsId int not null primary key auto_increment, FruitsName varchar(50), 
FruitsQuantity varchar(50), FruitsCost int);

The "create table" statement creates a new table called Fruits with columns FruitsId, FruitsName, FruitsQuantity, and FruitsCost. The FruitsId column is defined as an integer that cannot be null and will automatically increment for each new record and is designated as the primary key for the table. The FruitsName column is defined as a string that can be null. The FruitsQuantity column is defined as a string that also can be null. The FruitsCost column is defined as an integer that can be null.

Inserting data into the table

insert into Fruits (FruitsName, FruitsQuantity, FruitsCost) values 
("Apple", "1Kg", 100), ("Pineapple", "2Kg", 140), ("Banana", "1dozen", 90), 
("Pomegranate", "2Kg", 70),("Papaya", "1Kg", 40), ("Grapes", "5Kg", 80), 
("Mango", "5Kg", 155), ("Orange", "2Kg", 70), ("Berries", "3Kg", 90), ("Cherry", "2Kg", 85), ("Watermelon", "2Kg", 50);

This inserts eleven new records into the Fruits table with the specified values.

Note. Insert multiple records at once by separating the sets of values with commas. Each set of values must be enclosed in parentheses and separated by a comma.

Retrieving data from the table

select * from Fruits;

fruits_table_image

This query retrieves data from the Fruits table and returns all records for all columns.

Using AVG() function to find the average cost of fruits

Select Avg(FruitsQuantity) Avg_Fruits_quantity from Fruits;

We now use the AVG() function to find the average cost of all the fruits in the "Fruits" table. The "select" statement is used to select the AVG() function applied to the "FruitsCost" column. The result of the AVG() function is a single value representing the average cost of all the fruits.

Using AVG() function with a where clause

Select Avg(FruitsQuantity) Avg_Fruits_quantity from Fruits where FruitsQuantity>=3;

Avg_cost_of_fruits_table_with_where

Here we are using the AVG() function with a WHERE clause to find the average cost of all the fruits that have a quantity greater than or equal to 3. The "where" clause specifies the condition that must be fulfilled

Conclusion

In this article, I have explained AVG() Function with examples. It can be used to perform complex calculations and analyze data in various ways. I hope this will be useful for you.

FAQ's

Q 1- What is the return type of AVG() in MySQL?

A- The AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL).

Q 2- How do you round the result of the AVG() function in MySQL?

A- We can use the ROUND() function to round the result of the AVG() function to a specific number of decimal places.

SELECT ROUND(AVG(column_name), num_decimal_places) FROM table_name;

Q 3- Does AVG() in MySQL take NULL values?

A- AVG() function does not consider the NULL values during its calculation.