Analytical functions

Analytic functions compute an aggregate value based on a group of rows.

1)return deptartments and their employee count. non group by columns not allowed in select clause.
SELECT deptno,
COUNT(*) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30)
GROUP BY deptno;

DEPTNO DEPT_COUNT
---------------------- ----------------------
20 5
30 6

Using Analytical Function Correct query

SELECT empno,deptno,count(*) over(partition by deptno)dept_count
FROM emp where deptno in(10,20);

EMPNO DEPTNO DEPT_COUNT
7934 10 3
7782 10 3
7839 10 3
7902 20 5
7876 20 5
7566 20 5
7369 20 5
7788 20 5

For Reference http://www.orafaq.com/node/55