CODEWITHSUNDEEP
sqlpostgresqlwindow-functionscase-when
Tom Tom
Tom Tom

Reputation: 352

How to use division with window function and Case when statement

it work with sum() but not sum()/sum()

work with sum():

select *, 
case when time_of_day_viewers_time_zone >= '06:00:00 -
06:59:59' and time_of_day_viewers_time_zone <= '10:00:00 - 10:59:59'
then sum(amount_spent_usd) over () else null end as cpm 
from t1

not work with sum()/sum():

select *,
case when time_of_day_viewers_time_zone >= '06:00:00 - 06:59:59'
and time_of_day_viewers_time_zone <= '10:00:00 - 10:59:59'
then sum(amount_spent_usd)/sum(impressions)*1000 
over () else null end as cpm
from t1

edit1: even i use sum(amount_spent_usd)/nullif(sum(impressions),0)*1000 it still said 'syntax error at or near "over" '

edit2:

sum(amount_spent_usd) over () / (sum(impressions) over () * 1000)

if i use this query, it sums all 24 hour , it means that the case when statement is not applied. How to fix this?

edit4: finally i use this query although it long..

 (sum(case when time_of_day_viewers_time_zone >= '06:00:00 - 06:59:59'
and time_of_day_viewers_time_zone <= '10:00:00 - 10:59:59'
then amount_spent_usd else null end) over () /sum(case when time_of_day_viewers_time_zone >= '06:00:00 - 06:59:59'
and time_of_day_viewers_time_zone <= '10:00:00 - 10:59:59'
then impressions else null end) over ())*1000 as new

thanks in advance

Upvotes: 0

Views: 1191

Answers (1)

user330315
user330315

Reputation:

You can't use over() for two window functions, you need to provide the windowing clause for each function separately. And the multiplication needs to be done on the whole expression:

sum(amount_spent_usd) over () / (sum(impressions) over () * 1000)

Upvotes: 1

Related Questions