ROUND function is used to
round off a number to nearest decimal value. ROUND function takes two
arguments, If you don't specify second argument, it round off to nearest
integer.
Syntax
ROUND(number)
ROUND(number, decimalPoint)
mysql> SELECT
ROUND(10.07);
+--------------+
| ROUND(10.07) |
+--------------+
| 10 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT
ROUND(10.70);
+--------------+
| ROUND(10.70) |
+--------------+
| 11 |
+--------------+
1 row in set (0.00 sec)
I am going to use following
sample data.
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)
Apply ROUND function on above
query.
mysql> SELECT ROUND(AVG(salary)) FROM employee; +--------------------+ | ROUND(AVG(salary)) | +--------------------+ | 1859595 | +--------------------+ 1 row in set (0.00 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)
Apply ROUND function on above
query.
mysql> SELECT empInfo.city, ROUND(AVG(emp.salary)) FROM employee emp, employee_info empInfo WHERE emp.id=empInfo.id GROUP BY empInfo.city; +-----------+------------------------+ | city | ROUND(AVG(emp.salary)) | +-----------+------------------------+ | Bangalore | 828560 | | Chennai | 87698 | | Chirala | 1234579 | | Nellore | 76544 | | Punuru | 9876544 | +-----------+------------------------+ 5 rows in set (0.00 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)
Apply ROUND function on above
query.
mysql> SELECT empInfo.city, ROUND(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 | 76544 | | Chennai | 87698 | | Bangalore | 828560 | | Chirala | 1234579 | | Punuru | 9876544 | +-----------+----------------+ 5 rows in set (0.00 sec)
No comments:
Post a Comment