Group by vs Partition by in Oracle
They are not the same.
This will return 3 rows:
select deptno, count(*) c from emp group by deptno;DEPTNO C------ -10 320 530 6This will return 14:
select deptno, count(*) over (partition by deptno) c from emp;DEPTNO C------ -10 310 310 320 520 520 520 520 530 630 630 630 630 630 6
Check this linkThe main difference between aggregate and analytic functions is that though analytic functions give aggregate result they do not group the result set. They return the group value multiple times with each record.
With PARTITON BY it is posible to do this in one query to get different calculations or group by.
select DISTINCT deptno, count(*) over (partition by deptno) c, COUNT(*) OVER (PARTITION BY NULL) AS TOTALfrom emp;