All Forums Database
mcfabrero 6 posts Joined 09/13
28 Mar 2016
Encountered an error using SUM Over Partition by

Hi All,
I'm getting an error regarding non aggregate values when using sum over partition function. Basically, my goal is to get the total of a measured column for a particular descriptive column. i.e. 



A.Product Type,



sum(B.Topup) over ( partition by A.Dateid, A.Product_Type,A.Region rows unbounded preceding)


Table1 A


Table 2 B


A.dateid = B.dateid


From sample query, I need to get the total of topup per Date, product type and region but I'm getting an error 3504 stating that "selected non aggregate values  must be part of the associated group"


Am I using the correct syntax in order to get the expected result? Could you also help shine a light on how over partition function or OLAP functions should be use?


Your help is greatly appreciated.







nanipsns 7 posts Joined 08/13
29 Mar 2016

Hey MC,

You missed to add GROUP BY at the end of the statement.

add "Group by 1,2,3" and try executing the query. That should work.

sakthikrr 53 posts Joined 07/12
29 Mar 2016

Hi Narasimha,
But query fails still since columns which are used in partition by clause are not aggregated.


ToddAWalter 316 posts Joined 10/11
29 Mar 2016

It sounds like you want the total top up and total revenue for each combination of date, product, region. If that is correct, remove the over... clause completely and use the group by. 
If you take the sum(revenue) item out of the select list and execute the query without the group by, you will see that the over clause will calculate the running sum of top up across all the rows in the table, starting from zero for each new combination of date, product, region. From your description it does not sound like the running sum is what you desire. 

mcfabrero 6 posts Joined 09/13
28 May 2016

Hi All,
Can you please explain and give sample queries on how SUM Over partition is used? As per checking it is not possible to only used this function for one aggregated column, thus if your query has columns aggregated, all should be using over partition function. 

You must sign in to leave a comment.