Encountered an error using SUM Over Partition by

Database
Enthusiast

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. 

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

4 REPLIES
Enthusiast

Re: Encountered an error using SUM Over Partition by

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.

Enthusiast

Re: Encountered an error using SUM Over Partition by

Hi Narasimha,

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

Teradata Employee

Re: Encountered an error using SUM Over Partition by

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. 

Enthusiast

Re: Encountered an error using SUM Over Partition by

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!