Powered By Blogger

Monday 12 September 2016

Difference between Group By and Partition By.





Group By Clause
Partition By Clause
Reduces the no. of records
No. of records will not be reduced
Any non group by column is not allowed in the select clause. Example :
SELECT deptno
,COUNT(*) DEPT_COUNT
FROM emp
GROUP BY deptno;
Any non group by column is  allowed in the select clause. Example:
SELECT empno, deptno
,COUNT(*) OVER (PARTITION BY deptno) DEPT_COUNT
FROM emp;
Group by actually groups the result set returning one row per group.
Analytic functions (Partition by) give aggregate result they do not group the result set means They return the group value multiple times with each record.