Analytical functions I
Group functions and Analytical functions
Group functions operate on a set of rows to give one result of a group Where as Analytic functions return multiple rows for each group.
Ex 1:- SELECT deptno, count(*)deptcount
FROM emp
GROUP BY deptno
EX 2:- SELECT empno, ename deptno,
COUNT(*) OVER (PARTITION BY deptno) deptcount
FROM emp
The PARTITION BY clause is just like group by logically breaks a single result set into groups.
In absence of any PARTITION inside the OVER( ) portion, the function acts on entire record set
Ex 3:- SELECT empno, ename deptno,
COUNT(*) OVER () deptcount
FROM emp