MySQL Average

Introduction to MySQL Average

To find the average of a field in various records, we use MySQL Average. We can find the average of the records of a column by using the “Group by” clause.MySQL AVG() function is an aggregate function that allows you to calculate the average value of column values. To calculate the average of the distinct values from a column we can use the “DISTINCT” operator. AVG function will ignore “NULL” values.

AVG function can be used in the subqueries. It can be used along with the control flow functions as well like IF, IFNULL, NULLIF, and CASE.

Syntax:

SELECT AVG(EXPRESSION) FROM <TABLE_NAME>;

Here above is the syntax of the Average function. The average function returns the data of INT datatype.

How Does MySQL Average Work?

Now let us create a table and perform the average function on the column and retrieve the data.

Query:

create table Freelancer_data
(
Freelancer_id INT,
Freelancer_Name VARCHAR(20),
Type_of_work VARCHAR(30),
No_of_submission INT,
No_of_pages_submitted INT,
EMAIL varchar(30)
);

1. Insert data Into the Table

Query:

insert into freelancer_data values (1278,'Jack','Typist', 2, 300,'[email protected]');
insert into freelancer_data values (2278,'Will','Artical Writer', 3, 450,'[email protected]');
insert into freelancer_data values (3278,'Rose','Artical Writer', 3, 540,'[email protected]');
insert into freelancer_data values (4278,'Ben','Typist', 3, 860,'[email protected]');
insert into freelancer_data values (5278,'Stuart','Typist', 5, 600,'[email protected]');
insert into freelancer_data values (6278,'Rample','Artical Writer', 6, 900,'[email protected]');
insert into freelancer_data values (7278,'Jackern','Typist', 3, 700,'[email protected]');
insert into freelancer_data values (8278,'fred','Artical Writer', 2, 300,'[email protected]');
insert into freelancer_data values (9278,'Gram','Artical Writer', 9, 400,'[email protected]');

2. Select the Data from the Table

Query:

select * from freelancer_data;

Output:

3. Now let us find the average Pages Submitted by the Freelancer

Query:

select AVG(No_of_pages_submitted) as "Average papers submitted" from freelancer_data;

Output:

Now let us find the average pages submitted by the freelancer based on the Type_of_work using “GROUP BY” clause:

Query:

select AVG(No_of_pages_submitted) as "Average papers submitted", Type_of_work from freelancer_data group by 2;

Output:

or

Query:

select AVG(No_of_pages_submitted) as "Average papers submitted", Type_of_work from freelancer_data group by Type_of_work;

Output:

4. Using AVG() With “HAVING” Clause

To set conditions to the output of the average values, we use the “HAVING” clause.

Query:

select AVG(No_of_pages_submitted) as "Average papers submitted", Type_of_work
from freelancer_data
group by 2
having AVG(No_of_pages_submitted) > 600;

or

Query:

select AVG(No_of_pages_submitted) as "Average papers submitted", Type_of_work
from freelancer_data
group by Type_of_work
having AVG(No_of_pages_submitted) > 600;

Output:

5. Using AVG() with sub-query

Here in the sub-query, we are finding the average based on “type_of_work”. The outer query gets the average for the output of the inner query.

Query:

SELECT
AVG(AVG_PAGES) as "average pages"/* Outer query*/
FROM
(select
AVG(NO_OF_PAGES_SUBMITTED) AS "AVG_PAGES" /* -- inner query --*/
from
freelancer_data
GROUP BY TYPE_OF_WORK) AVG;

Output:

6. Using AVG() with Control functions

Here let us find the average of the pages submitted if the “no_of_submission” is 3 else consider as “null”. As, AVG ignores the NULL values the below output is average for only the count of submission is =3.

Query:

SELECT
AVG(IF(No_of_submission= 3,
No_of_pages_submitted,
NULL))/No_of_submission 'Avg pages'
FROM
freelancer_data;

Output:

Example to Implement MySQL Average

Now let us consider other simple examples below:

Query:

create table sample_AVG
(
ID INT,
NAME VARCHAR(30),
DEPT_NO INT,
SALARY FLOAT(10,2)
);

1. Insert data Into the Table

Query:

insert into SAMPLE_AVG values (1278,'Jack', 2, 90000);
insert into SAMPLE_AVG values (2278,'Will', 2, 80000);
insert into SAMPLE_AVG values (3278,'Rose', 3, 78000);
insert into SAMPLE_AVG values (4278,'Ben', 3, 45000);
insert into SAMPLE_AVG values (5278,'Stuart', 3, 67000);
insert into SAMPLE_AVG values (6278,'Rample', 4, 57000);
insert into SAMPLE_AVG values (7278,'Jackern', 4, 47000);
insert into SAMPLE_AVG values (8278,'fred', 4, 68000);
insert into SAMPLE_AVG values (9278,'Gram', 4,86000);

Query:

select * from SAMPLE_AVG;

Output:

2. Now let us find the average of the salary from the Table

Query:

select AVG(salary) as "Average salary" from sample_avg;

Output:

3. Now let us find the average SALARY based on the DEPT_NO using the “GROUP BY” Clause

Query:

select AVG(salary) as "Average salary", Dept_no from sample_avg group by 2; /* - - Position of the column - -*/

Output:

or

Query:

select AVG(salary) as "Average salary", Dept_no from sample_avg group by dept_no;

Output:

4. Using AVG() With “HAVING” Clause

To set conditions to the output of the average values, we use the “HAVING” clause.

Query:

select
AVG(salary) as "Average salary",
Dept_no from sample_avg
group by 2
having AVG(salary) > '65000'

or

Output:

Query:

select
AVG(salary) as "Average salary",
Dept_no from sample_avg
group by dept_no
having AVG(salary) > '65000'

Output:

5. Using AVG() with sub-query

Here in the sub-query we are finding the average based on “dept_no”. The outer query gets the average for the output of the inner query.

Query:

SELECT
AVG(AVG_SAL) as "average salary"
FROM
(select
AVG(salary) as "Avg_sal",
Dept_no from sample_avg
group by 2
)AVG;

Output:

6. Using AVG() with Control Functions

Here let us find the average of the SALARY if the “DEPT_NO” is in 3, 4 else consider as “null”. As, AVG ignores the NULL values the below output is average for only the count of submission is 3 and 4.

Query:

SELECT
AVG(IF(DEPT_NO IN (3,4),
SALARY,
NULL))/COUNT(DEPT_NO) 'AVGSALARY'
FROM
SAMPLE_AVG;

Output: 

Conclusion

• To find the average of a field in various records, we use MySQL Average. We can find the average of the records of a column by using the “Group by” clause.
• MySQL AVG() function is an aggregate function that allows you to calculate the average value of column values.
• To calculate the average of the distinct values from a column we can use the “DISTINCT” operator. AVG function will ignore “NULL” values.
• AVG function can be used in the subqueries. It can be used along with the control flow functions as well like IF, IFNULL, NULLIF, and CASE.

Recommended Articles

This is a guide to MySQL Average. Here we discuss the Introduction of MySQL Average and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –

1. Introduction to MySQL Operators
2. Top 23 MySQL String functions
3. MySQL vs SQLite | Top 14 Comparisons
4. Guide to MySQL Timestamp