AVG function

The AVG() function returns the average value of a numeric column.

CREATE TABLE employee(

  id int PRIMARY KEY,

  name VARCHAR(25),

  age int,

  salary DECIMAL(10, 4),

  mailId VARCHAR(25),

  city VARCHAR(10)

);

INSERT INTO employee VALUES (1, "Hari", 28, 12345.67, "hari@hari.com", "Bangalore");

INSERT INTO employee VALUES (2, "Sandesh", 31, 98345.00, "sandesh@sandesh.com", "Trivendram");

INSERT INTO employee VALUES (3, "Phalgum", 33, 119345.67, "phalgun@hari.com", "Hyderabad");

INSERT INTO employee VALUES (4, "Manju", 36, 87666.87, "manju@sandesh.com", "Bangalore");

INSERT INTO employee VALUES (5, "Rakesh", 26, 38000, "rakesh@hari.com", "Bangalore");

INSERT INTO employee VALUES (6, "Sankalp", 38, 87645.67, "sankalp@sankalp.com", "Chenai");

INSERT INTO employee VALUES (7, "Vadiraj", 40, 12345.67, "vadi@hari.com", "Bangalore");

INSERT INTO employee VALUES (8, "Prasob", 37, 12345.67, "prasob@sandesh.com", "Trivendram");

INSERT INTO employee (id, name, salary) VALUES (9, "Kesav", 123457.89);

CREATE TABLE employee_info(

  id int PRIMARY KEY,

  city VARCHAR(20),

  state VARCHAR(20),

  country VARCHAR(20)

);

INSERT INTO employee_info VALUES (1, "Bangalore", "Karnataka", "India");

INSERT INTO employee_info VALUES (2, "Chirala", "Andhra Pradesh", "India");

INSERT INTO employee_info VALUES (3, "Punuru", "Andhra Pradesh", "India");

INSERT INTO employee_info VALUES (4, "Bangalore", "Karnataka", "India");

INSERT INTO employee_info VALUES (5, "Bangalore", "Karnataka", "India");

INSERT INTO employee_info VALUES (6, "Nellore", "Andhra Pradesh", "India");

INSERT INTO employee_info VALUES (7, "Chennai", "Tamilnadu", "India");

INSERT INTO employee_info VALUES (17, "Bhuvaneswar", "Orissa", "India");

INSERT INTO employee_info VALUES (27, "Mumbai", "Maharastra", "India");

INSERT INTO employee_info VALUES (37, "Delhi", "Delhi", "India");

mysql> SELECT * FROM employee;
+----+--------------+------------+------------+
| id | firstName    | lastName   | salary     |
+----+--------------+------------+------------+
|  1 | Hari Krishna | Gurram     |   12345.67 |
|  2 | Rama Devi    | Gurram     | 1234578.67 |
|  3 | Lakshmana    | Rao        | 9876543.67 |
|  4 | Rama         | Krishna    | 1234587.67 |
|  5 | Sowmya       | asd        | 1238745.67 |
|  6 | Jyotsna      | PS         |   76543.67 |
|  7 | Gireesh      | Amara      |   87698.00 |
|  8 | Sravani      | Nidamanuri |  987654.00 |
|  9 | Saranya      | Amara      | 1987654.00 |
+----+--------------+------------+------------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM employee_info;
+----+-------------+----------------+---------+
| id | city        | state          | country |
+----+-------------+----------------+---------+
|  1 | Bangalore   | Karnataka      | India   |
|  2 | Chirala     | Andhra Pradesh | India   |
|  3 | Punuru      | Andhra Pradesh | India   |
|  4 | Bangalore   | Karnataka      | India   |
|  5 | Bangalore   | Karnataka      | India   |
|  6 | Nellore     | Andhra Pradesh | India   |
|  7 | Chennai     | Tamilnadu      | India   |
| 17 | Bhuvaneswar | Orissa         | India   |
| 27 | Mumbai      | Maharastra     | India   |
| 37 | Delhi       | Delhi          | India   |
+----+-------------+----------------+---------+
10 rows in set (0.00 sec)

Get average salary of all employees

mysql> SELECT AVG(salary) FROM employee;
+----------------+
| AVG(salary)    |
+----------------+
| 1859594.557778 |
+----------------+
1 row in set (0.01 sec)

Get average salary of employees city wise

mysql> SELECT empInfo.city, AVG(emp.salary) FROM employee emp, employee_info empInfo WHERE emp.id=empInfo.id GROUP BY empInfo.city;
+-----------+-----------------+
| city      | AVG(emp.salary) |
+-----------+-----------------+
| Bangalore |   828559.670000 |
| Chennai   |    87698.000000 |
| Chirala   |  1234578.670000 |
| Nellore   |    76543.670000 |
| Punuru    |  9876543.670000 |
+-----------+-----------------+
5 rows in set (0.01 sec)

Get average salary of employees city wise and sort them by average salary.

mysql> SELECT empInfo.city, AVG(emp.salary) AS employeeSalary FROM employee emp, employee_info empInfo WHERE emp.id=empInfo.id GROUP BY empInfo.city ORDER BY employeeSalary;
+-----------+----------------+
| city      | employeeSalary |
+-----------+----------------+
| Nellore   |   76543.670000 |
| Chennai   |   87698.000000 |
| Bangalore |  828559.670000 |
| Chirala   | 1234578.670000 |
| Punuru    | 9876543.670000 |
+-----------+----------------+
5 rows in set (0.00 sec)

Previous                                                 Next                                                 Home