MariaDB AVG Function

MariaDB is an open-source relational database management system that is a of MySQL and is based on SQL(Structured query language). It is an improved version of MySQL and has various features, security, and performance when compared to MySQL.

The MariaDB AVG function is used to calculate the average of values present in the column. The average returned is a decimal value. It can also remove duplicate values before computing the average.

Syntax:

AVG ( [ALL | DISTINCT] expr )

AVG Function

It is an aggregate function that returns the average of the resultant column values in decimal. It also allows two optional parameters ALL and DISTINCT, where ALL is the default parameter when any of these are not passed.

• ALL: This is the default parameter. If this parameter is used then duplicate values are also considered for computing average.
• DISTINCT: If this parameter is used then duplicate values are not considered for computing average.

Example of AVG Function

For better understanding of AVG Functions we need a table on which we will perform some operations. So we will create Table called employees which consist of employee_id, name, salary,and department_id as Columns.

Query:

CREATE TABLE employees 
(
  employee_id INT PRIMARY KEY,
  name VARCHAR(50),
  salary DECIMAL(10,2),
  department_id INT
);

Output:

Employees Table Schema

After Inserting Some Data into the Employees Table, Our Table Looks:

Output:

Inserted data in the Employees DB

Explanation: The image displays the employees table where we can check the 5 rows inserted.

Example 1: Average Salary of the Employees

The query provides the average of the employees salary in the whole table and name the column as average_salary.

Query

Output:

Average Salary of Employees

Explanation: The image displays the query and its resultant table which is the average salary of all the employees in the table.

Example 2: Average Salary of the Employees on Department Basis

The query provides the average salary for each department with respect to the department_id and the average salary of the department column is named as average_salary_per_department .

Query:

Query

Output:

Average Salary of the Employees on Department basis

Explanation: The image displays the query and its resultant table with the department_id and average_salary_per_department column.

Example 3: Average Salary of Over 40,000 of Departments

The query provides the average salary of employees in each department but only if its greater than 40,000.

SELECT department_id, AVG(salary) AS average_salary_per_department 
FROM employees 
GROUP BY department_id
 HAVING average_salary_per_department > 40000;

Output:

Average Salary of over 40,000 of Departments

Explanation: The image displays the query and its resultant table with the department_id and average_salary_per_department column which is greater than 40,000.

Example 4: Average Salary and Count of the Employee at the Same Time

The query provides the average salary name as average_salary along with the count of the employees name as employees_count in the employees table.

Query:

Query

Output:

Average Salary and count of the Employee at the same time

Explanation: The image displays the query and its resultant table with the employees_count and average_salary columns.

Conclusion

The AVG Function is very useful for computing averages and you can use it to get the average of distinct values of the required column. You use the average function along with the SELECT, FROM, WHERE queries without any problem. The text under parenthesis is the name of the column where the average will be displayed. This works for numeric values so don’t use it for column having other than numeric data type. The AVG Function is a powerful function for calculating the average of a set of values which is important for data analysis and exploration.