MariaDB AVG Function
Last Updated :
05 Jan, 2024
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.
Share your thoughts in the comments
Please Login to comment...