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.

Numbersnumbfish

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

Spadiceous

I am trying to understand. Correct me if i am wrong. so if i get distinct of the second query it will give me first query result . I have used partition only to do rank() over certain clause based on a partition column and get the first rank or so. In what circumstance the second query result will be useful? thank you – Numbersnumbfish

I would say that if it can be done using GROUP BY, that is preferable. Analytic functions should be used for cases where you want the individual rows plus some aggregate information - like the rank() example. – Spadiceous

Answer 2

Check this link The 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.

Mushro

Answer 3

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 TOTAL
from emp;

Rotative