Thread: Condition in a calculated field

Condition in a calculated field

From

JORGE MALDONADO

Date:

09 September 2014, 21:41:13

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

Re: Condition in a calculated field

From

David G Johnston

Date:

09 September 2014, 21:49:05

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.

Re: Condition in a calculated field

From

JORGE MALDONADO

Date:

09 September 2014, 22:00:25

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.g.johnston@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: Condition in a calculated field

From

JORGE MALDONADO

Date:

09 September 2014, 22:03:54

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@gmail.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.g.johnston@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: Condition in a calculated field

From

David Johnston

Date:

09 September 2014, 22:05:12

On Tue, Sep 9, 2014 at 3:00 PM, JORGE MALDONADO <jorgemal1960@gmail.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?

HAVING
facturas.fce_valor_comercial > total

It cannot see "total" because that alias is defined in the final select-list.

As I said you have to repeat the expression.

HAVING

facturas.fce_valor_comercial > sum(facsub.fce_valor_comercial)

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.