Vs Partition by group at Oracle

Question

I am writing a query to retrieve records from an Oracle repository. Its a simple select query with joins across multiple tables, and I have multiple columns to aggregate. Therefore, I am ending using Groupby on the rest of the columns.

Let's say I collect 10 columns, of which 5 are cumulative columns. so i need to group by other 5 columns. I can even achieve the same without doing Groupby and use the over (paritition by) clause for every every column of the aggregate I want to get.

I'm not sure if it’s better from stock or in general.

Answer 1

They do not match.

This will return 3 lines:

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 

Answer 2

Check out this link. The main difference between aggregate and analytic functions is that although analytic functions give aggregate results, they do not group the result set. They return a group value several times with each record.

Answer 3

With PARTITON BY you can do this in one query to get different calculations or a group.

 select DISTINCT deptno, count(*) over (partition by deptno) c, COUNT(*) OVER (PARTITION BY NULL) AS TOTAL from emp;