CODEWITHSUNDEEP
sqlpostgresql
Pablo Andrés Martínez Vargas
Pablo Andrés Martínez Vargas

Reputation: 405

Can't make NULLIF work using Window Functions of postgres 10

I'm trying to use NULLIF to avoid division by zero error when using the over window function of postgres.

It throws "syntax error at or near "over"."

I'm using this query:

  SELECT 
    dlo.nombre_cadena, dpr.marca_producto,
    sum(rep.mes_33_uni) / NULLIF(sum(sum(rep.mes_33_uni)), 0) over (partition by dpr.marca_producto) AS porcentajep,
    FROM
    rpro_dim.reporte33_full rep
JOIN rpro_dim.dim_productos_retail dpr ON dpr.id_dim_prod_retail = rep.id_dim_prod_retail
JOIN rpro_dim.dim_locales dlo ON dlo.id_dim_local = rep.id_dim_localgroup by 
dlo.nombre_cadena, dpr.marca_producto
order by dlo.nombre_cadena, dpr.marca_producto

Upvotes: 0

Views: 692

Answers (1)

klin
klin

Reputation: 121889

With

NULLIF(sum(sum(rep.mes_33_uni)), 0) over (partition by dpr.marca_producto) -- error!

you are trying to execute nullif() as a window function.

You should put the whole expression as an argument for nullif():

NULLIF(sum(sum(rep.mes_33_uni)) over (partition by dpr.marca_producto), 0)

Upvotes: 2

Related Questions