CODEWITHSUNDEEP
sqloracle-databasedata-warehouseanalytic-functions
user747858
user747858

Reputation:

Group by vs Partition by in Oracle

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.

Upvotes: 9

Views: 40198

Answers (3)

SLAVICA
SLAVICA

Reputation: 1

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;

Upvotes: -1

Anurag Peshne
Anurag Peshne

Reputation: 1547

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.

Upvotes: 7

Tony Andrews
Tony Andrews

Reputation: 132710

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

Upvotes: 21

Related Questions