From: | JORGE MALDONADO <jorgemal1960(at)gmail(dot)com> |
---|---|
To: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Condition in a calculated field |
Date: | 2014-09-09 19:00:16 |
Message-ID: | CAAY=A7-ntLABLcPF0yvJXbV9CfsRuvqQYHojteegXzhW2H3oTQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I just watched the documentation and did what you suggested but I am still
getting the same error message. I did the following test, what is wrong?
SELECT
facturas.fce_id,
facturas.fce_numero_factura,
facturas.fce_fecha,
compradores.com_nombre,
facturas.fce_subdivision,
facturas.fce_valor_comercial,
SUM(facsub.fce_valor_comercial) AS total
FROM
trafico.facturas_exportacion facturas
INNER JOIN trafico.cat_compradores compradores ON (facturas.fce_comprador
= compradores.com_clave)
INNER JOIN trafico.facturas_exportacion facsub ON (facturas.fce_id =
facsub.fce_id_factura_original)
WHERE
facturas.fce_subdivision = true
HAVING
facturas.fce_valor_comercial > total
GROUP BY facturas.fce_id,compradores.com_clave
On Tue, Sep 9, 2014 at 1:48 PM, David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com
> wrote:
> JORGE MALDONADO wrote
> > Can I use a calculated field in a WHERE condition of a SELECT statement?
> I
> > get a "column 'total' does not exist" error when I run the statement
> > below.
> > 'total' is a field generated by a SUM and I want to use it in the WHERE
> > condition.
> >
> > SELECT
> > facturas.fce_id,
> > facturas.fce_numero_factura,
> > facturas.fce_fecha,
> > compradores.com_nombre,
> > facturas.fce_subdivision,
> > facturas.fce_valor_comercial,
> > SUM(facsub.fce_valor_comercial) AS total
> > FROM
> > trafico.facturas_exportacion facturas
> > INNER JOIN trafico.cat_compradores compradores ON
> > (facturas.fce_comprador
> > = compradores.com_clave)
> > INNER JOIN trafico.facturas_exportacion facsub ON (facturas.fce_id =
> > facsub.fce_id_factura_original)
> > WHERE
> > facturas.fce_subdivision = true AND facturas.fce_valor_comercial >
> total
> > GROUP BY facturas.fce_id,compradores.com_clave
> >
> > Respectfully,
> > Jorge Maldonado
>
> WHERE clause is pre-grouping.
>
> What you want is the HAVING clause - though you have to repeat the
> expression (e.g., col > sum(...) ) as opposed to referring to it by name
> (e.g., col > total).
>
> http://www.postgresql.org/docs/devel/static/sql-select.html
>
> David J.
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Condition-in-a-calculated-field-tp5818353p5818355.html
> Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
From | Date | Subject | |
---|---|---|---|
Next Message | JORGE MALDONADO | 2014-09-09 19:03:47 | Re: Condition in a calculated field |
Previous Message | David G Johnston | 2014-09-09 18:48:57 | Re: Condition in a calculated field |