Group by vs Partition by in Oracle

Question

I am writing a query to fetch records from a Oracle warehouse. Its a simple Select Query with joins on few tables and i have few columns to be aggregated. Hence i end up using Groupby on rest of the columns.

Say I am picking some 10 columns and out of which 5 is aggregate columns. so i need to group by on the other 5 columns. I can even achieve the same by not doing a Groupby and using over (paritition by) clause on the each each aggregate column i want to derive.

I am not sure which is better against a warehouse or in general.

Answer 1

They are not the same.

This will return 3 rows:

select deptno, count(*) c from emp group by deptno;

DEPTNO C
------ -
10     3
20     5
30     6

This will return 14:

select deptno, count(*) over (partition by deptno) c from emp;


DEPTNO C
------ -
10     3
10     3
10     3
20     5
20     5
20     5
20     5
20     5
30     6
30     6
30     6
30     6
30     6
30     6