Round an AVG function
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL> create table job(
2 empno Number(3) NOT NULL, -- Employee ID
3 jobtitle VARCHAR2(10 BYTE) -- Employee job title
4 )
5 /
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,'Alison',to_date('19960321','YYYYMMDD'), 45000, 48000, 'E')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, 'James',to_date('19781212','YYYYMMDD'), 23000, 32000, 'W')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,'Celia',to_date('19821024','YYYYMMDD'), 53000, 58000, 'E')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,'Robert',to_date('19840115','YYYYMMDD'), 31000, 36000, 'W')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,'Linda', to_date('19870730','YYYYMMDD'), 43000, 53000, 'E')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,'David', to_date('19901231','YYYYMMDD'), 78000, 85000, 'W')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,'Jode', to_date('19960917','YYYYMMDD'), 21000, 29000, 'E')
3 /
1 row created.
SQL>
SQL> select * from employee;
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 48000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 53000 58000 E
105 Robert 15-JAN-84 31000 36000 W
116 Linda 30-JUL-87 43000 53000 E
117 David 31-DEC-90 78000 85000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL>
SQL> -- Query with a ROUND function
SQL>
SQL> SELECT empno, ename, orig_salary,
2 ROUND(AVG(orig_salary) OVER()) "Avg. salary"
3 FROM employee
4 ORDER BY ename;
EMPNO ENAME ORIG_SALARY Avg. salary
---------- ---------- ----------- -----------
122 Alison 45000 42000
104 Celia 53000 42000
117 David 78000 42000
123 James 23000 42000
108 Jode 21000 42000
116 Linda 43000 42000
105 Robert 31000 42000
7 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Related examples in the same category
1. | ROUND: Returns the number rounded to nearest value (precision adjustable) | | |
2. | Syntax: ROUND(,) | | |
3. | Specifying negative precision will round numbers on the left side of the decimal point, as shown here: | | |
4. | ROUND(5.75) | | |
5. | Simple demo for ROUND: round a number | | |
6. | ROUND with precision | | |
7. | ROUND(44.647, -1) | | |
8. | ROUND(Number,1): round values from column | | |
9. | ROUND(Number,0) | | |
10. | ROUND(Number,-1) | | |
11. | Use ROUND function in PL/SQL | | |
12. | round Demo | | |
13. | Round date to day | | |
14. | Round price as new price | | |
15. | Round result from months_between | | |
16. | Rounding Up and Down | | |
17. | Rounds 7:45:26 P.M. on May 25, 2005, to the nearest hour | | |
18. | Rounds May 25, 2005, to the first day in the nearest month | | |