Using Analytical Functions
select
ename,
dname,
sal,
sum(sal) over (partition by dname) dept_wise_total,
sum(sal) over (partition by dname order by dname, ename) deptwise_running_total,
sum(sal) over () total,
sum(sal) over (order by dname, ename) running_total
from
scott.emp e,
scott.dept d
where
e.deptno=d.deptno
order by
dname, ename
---------------------------------------------------------------------------------------------------
Simulated Version
select
ename,
dname,
sal,
sum(sal) over (partition by dname order by dname, ename) deptwise_running_total,
(select sum(sal) from scott.emp b where b.deptno=e.deptno and b.ename <= e.ename) sim_dept_runtotal,
sum(sal) over (order by dname, ename) running_total,
(select sum(sal) from scott.emp b where b.ename <= e.ename) sim_running_total
from
scott.emp e,
scott.dept d
where
e.deptno=d.deptno
order by
dname, ename
Output
Related Links:
- Oracle: Getting Cumulative Sum (Running Total) Using Analytical Functions
- Top N query - 3rd highest salary
- Oracle: Rank, Dense_Rank, Row_Number Analytic Functions
- LAG and LEAD - Analytic Function
- FIRST_VALUE and LAST_VALUE with Windowing Clause
select
ename,
dname,
sal,
sum(sal) over (partition by dname) dept_wise_total,
sum(sal) over (partition by dname order by dname, ename) deptwise_running_total,
sum(sal) over () total,
sum(sal) over (order by dname, ename) running_total
from
scott.emp e,
scott.dept d
where
e.deptno=d.deptno
order by
dname, ename
---------------------------------------------------------------------------------------------------
Simulated Version
select
ename,
dname,
sal,
sum(sal) over (partition by dname order by dname, ename) deptwise_running_total,
(select sum(sal) from scott.emp b where b.deptno=e.deptno and b.ename <= e.ename) sim_dept_runtotal,
sum(sal) over (order by dname, ename) running_total,
(select sum(sal) from scott.emp b where b.ename <= e.ename) sim_running_total
from
scott.emp e,
scott.dept d
where
e.deptno=d.deptno
order by
dname, ename
Output
Related Links:
- Oracle: Getting Cumulative Sum (Running Total) Using Analytical Functions
- Top N query - 3rd highest salary
- Oracle: Rank, Dense_Rank, Row_Number Analytic Functions
- LAG and LEAD - Analytic Function
- FIRST_VALUE and LAST_VALUE with Windowing Clause
No comments:
Post a Comment