ORDER BY - brings up the running total/sum by certain repeating column
PARTITION BY - brings up the GROUP total by certain repeating column
Consider following simple table.
This projects example contains 2 projects - ABC and CAB
Each project contains 2 tasks and its cost respectively.
By using same Analytic functions with two different window, can get "running totals" and "group totals"
select project_id, project_name, task_no, cost
,sum(cost) over(order by project_id,task_no) running_total_by_project_task
,sum(cost) over(order by project_id) running_total_by_project
,sum(cost) over(partition by project_id) total_cost_by_project
,sum(cost) over(partition by task_no) total_cost_by_task
from temp_project_details;
Output below..
1 comment:
Nice article and explanation is good,Thank you for sharing your experience on oracle Analytic Functions.It is very useful and informative article
Oracle Fusion cloud HCM Online Training
Post a Comment