How the AVG() function works in Mariadb?

The AVG() function is an aggregate function in MariaDB that calculates the average value of a given set of values. It is commonly used in statistical analysis and reporting to find the central value of numeric data.

Syntax

The syntax for the MariaDB AVG() function is as follows:

AVG(expression)

The expression parameter typically refers to a column of numbers for which the average is to be calculated. The function returns the average value of the expression as a decimal number.

Examples

Example 1: Basic Usage of `AVG()`

First, we create a table with sample data:

DROP TABLE IF EXISTS student_scores;
CREATE TABLE student_scores (id INT, name VARCHAR(50), score INT, passed VARCHAR(3));
INSERT INTO student_scores VALUES (1, 'Alice', 90, 'Yes'), (2, 'Bob', 85, 'Yes'), (3, 'Charlie', 75, 'No'), (4, 'Diana', 80, 'Yes');

Now, we can calculate the average score for all students:

SELECT AVG(score) FROM student_scores;

+------------+
| AVG(score) |
+------------+
|    82.5000 |
+------------+

The output displays the average score of all students in the student_scores table.

Example 2: Average of Distinct Values

Calculating the average of distinct values in a column to avoid duplication in the calculation.

SELECT AVG(DISTINCT score) FROM student_scores;

+---------------------+
| AVG(DISTINCT score) |
+---------------------+
|             82.5000 |
+---------------------+

The output is the average of distinct scores, eliminating any duplicates.

Example 3: Average with Condition

Here, we calculate the average score of students who passed the exam.

SELECT AVG(score) FROM student_scores WHERE passed = 'Yes';

+------------+
| AVG(score) |
+------------+
|    85.0000 |
+------------+

The output shows the average score of students who have passed.

Example 4: Average in a Group

In this example, we find the average score of students grouped by their ‘passed’.

SELECT passed, AVG(score) FROM student_scores GROUP BY passed;

+--------+------------+
| passed | AVG(score) |
+--------+------------+
| No     |    75.0000 |
| Yes    |    85.0000 |
+--------+------------+

The output indicates the average score for each class.

Below are a few functions related to the MariaDB AVG() function:

MariaDB SUM() function is used to calculate the total sum of a numeric column.
MariaDB COUNT() function returns the number of rows that match a specified criterion.
MariaDB MAX() function gets the maximum value from a set of values.
MariaDB MIN() function retrieves the minimum value from a set of values.

Conclusion

The AVG() function in MariaDB is a powerful tool for data analysis, allowing users to quickly calculate the average value of a set of numbers. It is particularly useful in scenarios where understanding the central tendency of data is crucial, such as in financial reporting, academic grading, or performance metrics. By combining the AVG() function with other aggregate functions and SQL clauses, you can perform complex data analysis and gain valuable insights from your databases.