MariaDB AVG
To get the average value of an expression, the MariaDB AVG function is used.
Syntax 1:
SELECT expressions, AVG (aggregate_expression) FROM table_name WHERE conditions;
Syntax 2: To calculate average and grouping the results by one or more columns.
SELECT expression1, expression2, ... expression_n, AVG(aggregate_expression) FROM tables WHERE conditions GROUP BY expression1, expression2, ... expression_n;
Parameters:
aggregate_expression: It is used to specify the column or expression that will be averaged.
Example 1: Using Group By Clause.
Players Table:
ID NAME SPORTS INCOME 1 Sachin Cricket 5000000 2 Dhoni Cricket 8000000 3 Sunil Football 2000000 4 Srikanth Badminton 1000000 5 Mary Boxing 3000000
Query:
SELECT sports, AVG(income) AS “Avg Income” FROM Players GROUP BY sports; |
Output:
SPORTS Avg Income Cricket 6500000 Football 2000000 Badminton 1000000 Boxing 3000000
Explanation:
The ‘Players’ is an already existing table. Here we are retrieving the average value of the income of the players of the same SPORTS group, using the Group By clause with an aggregate function AVG.
Example 2:
Players Table:
ID NAME SPORTS INCOME 1 Sachin Cricket 5000000 2 Dhoni Cricket 8000000 3 Sunil Football 2000000 4 Srikanth Badminton 1000000 5 Mary Boxing 3000000
Query:
SELECT AVG(income) FROM Players; |
Output:
3800000 |
Explanation:
The Average value of the INCOME is the result.
Example 3: Using Formula.
Players Table:
ID NAME SPORTS INCOME 1 Sachin Cricket 5000000 2 Dhoni Cricket 8000000 3 Sunil Football 2000000 4 Srikanth Badminton 1000000 5 Mary Boxing 3000000
Query:
SELECT AVG(income/2) FROM Players; |
Output:
1900000 |
Explanation:
The Average value of the INCOME which is divided by 2 is the result.