Encountered an error using SUM Over Partition by

[mcfabrero]

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. 

SELECT

A.Dateid

A.Product Type,

A.Region,

sum(B.Revenue),

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

from

Table1 A

LEFT JOIN

Table 2 B

ON 

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.

Thanks!

MC

[nanipsns]

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]

Hi Narasimha,

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

[ToddAWalter]

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]

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. 

Thanks!