Postgresql Can't filter a query using sum of field

Question

actually I need get all the rows that have a SUM(row) > 0, but when I make the sentence and execute it:

select o.corden,
    o.cproveedor,
    o.fecha_orden,
    o.status,
    o.monto_total,
    SUM(od.cant_restante) as cant_pend,
    COUNT(od.cart_comercial) as articulos,
    d.nombre as proveedor
from ordencompra o
inner join ordencompra_det od on o.corden = od.corden_det
inner join proveedores p on o.cproveedor = p.cproveedor
inner join data d on p.cdata = d.cdata
where o.status = 'PROCESADA'
    and cant_pend > 0
group by o.corden,
    d.nombre
order by o.corden asc

it said: The column << cant_pend >> doesn't exist! So i change it to:

select o.corden,
    o.cproveedor,
    o.fecha_orden,
    o.status,
    o.monto_total,
    SUM(od.cant_restante) as cant_pend,
    COUNT(od.cart_comercial) as articulos,
    d.nombre as proveedor
from ordencompra o
inner join ordencompra_det od on o.corden = od.corden_det
inner join proveedores p on o.cproveedor = p.cproveedor
inner join data d on p.cdata = d.cdata
where o.status = 'PROCESADA'
    and SUM(od.cant_restante) > 0
group by o.corden,
    d.nombre
order by o.corden asc

agregate function's can't be allowed on where clausule

Any help with the sentence?

Juan Carlos Oropeza · Accepted Answer · 2017-03-27 18:55:04Z

2

You want having clause:

select o.corden,
    o.cproveedor,
    o.fecha_orden,
    o.status,
    o.monto_total,
    SUM(od.cant_restante) as cant_pend,
    COUNT(od.cart_comercial) as articulos,
    d.nombre as proveedor
from ordencompra o
inner join ordencompra_det od on o.corden = od.corden_det
inner join proveedores p on o.cproveedor = p.cproveedor
inner join data d on p.cdata = d.cdata
where o.status = 'PROCESADA'
group by o.corden,
    d.nombre
having SUM(od.cant_restante) > 0
order by o.corden asc

edited Mar 27, 2017 at 18:55

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

answered Mar 27, 2017 at 18:34

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

JuJoGuAl Over a year ago

Is exactly what i just need it! Thanks :)

JuJoGuAl Over a year ago

Any difference between have the sql queries in mayus or minus?

Gurwinder Singh Over a year ago

@JuJoGuAl - Sorry? I didn't understand what you just asked.

JuJoGuAl Over a year ago

Any difference between have the sql queries in uppercase or lowercase* ?

Juan Carlos Oropeza Over a year ago

Depend on programer. I put all keywords in UPPER CASE rest is lower case to make it easy to read. But doesnt make different to the db

|

Collectives™ on Stack Overflow

Postgresql Can't filter a query using sum of field

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related