Group By an Partition by in a single query

Database
Enthusiast

Group By an Partition by in a single query

Hello All,

I know the functionality of group by function and partition by function. But when i use both the queries in the same query i get an error as Selected non-aggregate must be part of the group.

Below is the query i tried.

SEL

COL1,COL2,COL3,SUM(COL3),

SUM(COL2) OVER (PARTITION BY COL1,COL2,COL3)

FROM TABLE

GROUP BY 1,2,3

Please help me understand. Where i am going wrong.

Thanks in Advance.

Tags (2)
4 REPLIES
Enthusiast

Re: Group By an Partition by in a single query

Hi

These are my thoughts. Since you are aware of group by and partition by, I am not going in much details.

The above query should run. But if you change it to 

SEL

COL1,COL2,COL3,SUM(COL3),

SUM(COL2) OVER (PARTITION BY COL1,COL2,COL3)

FROM TABLE

then it should give you the same error as mentioned above. To run the aggregate operation SUM(COL3) with the specified selection, you need the group by clause. Whereas if you remove the SUM(COL3) also, then for the window aggregate function SUM() OVER() the group by clause is not required.

You can check the explain plan of the query. I am sure experts here can give you more details.

Thanks

Santanu

Enthusiast

Re: Group By an Partition by in a single query

Hi Santanu,

I had included the group by clause in my query. Still i was getting the above error i mentioned.

Senior Apprentice

Re: Group By an Partition by in a single query

Your query doesn't make sense, as the combination of COL1,COL2,COL3 is unique, so SUM(COL2) OVER (PARTITION BY COL1,COL2,COL3) returns the same as a simple COL2.

Of course you can use both GROUP BY and OLAP/PARTITION within the same query, but without showing your actual query it's hard to tell what you did wrong. The query you provided will run as-is.

Enthusiast

Re: Group By an Partition by in a single query

Thanks for the reply.

I do not have any query. I want to understand the logic as to how to apply both together.

Jugal