How to work with Avg Function in MySql
The AVG function is a mathematical function that will calculate the average of some values that we indicate.
Sql Syntax:
SELECT AVG(column name)
FROM table_name;Script:
--first let's drop the table Test (if she exists, if not skip the drop line).
Drop table test;
create table test (id int,name varchar(20),email varchar(20),salary int ,de char(2));
--insert values into table
insert into test values (1,'Eve','[email protected]',1500,'HR');
insert into test values (2,'Jon','[email protected]',2500,'AD');
insert into test values (3,'Mike','[email protected]',3000,'AD');
insert into test values (4,'Paul','[email protected]',3200,'HR');
insert into test values (5,'Mary','[email protected]',1800,'IT');
insert into test values (6,'Jane','[email protected]',2200,'IT');SQLselect * from test ;
ID NAME EMAIL SALARY DE
---------- -------------------- -------------------- ---------- -- --------
1 Eve [email protected] 1500 HR
2 Jon [email protected] 2500 AD
3 Mike [email protected] 3000 AD
4 Paul [email protected] 3200 HR
5 Mary [email protected] 1800 IT
6 Jane [email protected] 2200 ITExample 1:
-will try to find the average salary of all the persons on out table.SQLselect avg(salary) from test;
AVG(SALARY)
-----------
2366,66667Example 2:
-or see the average salary of all the persons that work in a specific department.SQLselect avg(salary) from test where de='IT';
AVG(SALARY
-----------
2000Example 3:
-or a more complex example is if we want to see the names of the persons that earn more than the average salary .SQLselect name from test
where salary(select avg(salary) from test);
NAME
--------------------
Jon
Mike
Paul