Re: Condition in a calculated field

I am sorry, the code I sent is wrong. This is the correct one:

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
GROUP BY
facturas.fce_id,compradores.com_clave
HAVING
facturas.fce_valor_comercial > total

On Tue, Sep 9, 2014 at 2:00 PM, JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>
wrote:

> 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
>>
>
>