CODEWITHSUNDEEP
sqlteradata
Victoria Ubaldo
Victoria Ubaldo

Reputation: 99

How to improve SQL Teradata with OVER PARTITION BY

I have a query with more than 7 columns with some counts() and sum() values, I was very slow, then I was trying using OVER PARTITION BY , but in the result table I have a error "Selected non-aggregate values must be part of the associated group".

This is the query :

insert into  SQUEMA.TABLE_B
select 
SUBSTR(cast(date as char(6)) ,1,4) ||'-'|| SUBSTR(cast(date as char(6)) ,5,6) as date ,
column1,
column2,
column3,
.
.
.
.
column7,
count(column8) OVER (PARTITION BY date, column1,...,column7) as column8
SUM(column9) OVER(ORDER BY date ) as column9
from  SQUEMA.TABLE_A tb 
where tb.date between '201711' and '201812'
group by date, column1,...,column7;

Error

Selected non-aggregate values must be part of the associated group

Upvotes: 1

Views: 2644

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

I believe you want:

sum(count(column8)) OVER (PARTITION BY date, column1,...,column7) as column8
sum(sum(column9))) OVER (ORDER BY date ) as column9

This is how you use window functions with aggregation.

Upvotes: 1

Related Questions