MySQL ODBC Connector - Help on Unknown field error

Author

Help on Unknown field error

pedro mpa

2006-01-31, 3:23 am

Greetings.

I need help on the following query.
I get an error like "Unknown/Invalid column total_price [...]" when I try
filter by total_price.
How can I do this correctly?

SELECT receipts.*,
(SELECT SUM(receipt_itens.price) FROM receipt_itens WHERE
receipt_itens.id_receipt=receipts.id) AS total_price
FROM receipts
WHERE
total_price >= ".$minprice." "
AND total_price <= ".$maxprice." "
ORDER BY receipts.date DESC

Thanks in advance.

Pedro.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Michael Stassen

2006-01-31, 3:23 am

pedro mpa wrote:
> Greetings.
>
> I need help on the following query.
> I get an error like "Unknown/Invalid column total_price [...]" when I try
> filter by total_price.
> How can I do this correctly?
>
> SELECT receipts.*,
> (SELECT SUM(receipt_itens.price) FROM receipt_itens WHERE
> receipt_itens.id_receipt=receipts.id) AS total_price
> FROM receipts
> WHERE
> total_price >= ".$minprice." "
> AND total_price <= ".$maxprice." "
> ORDER BY receipts.date DESC
>
> Thanks in advance.
>
> Pedro.

You can't use column aliases in the WHERE clause. Try changing "WHERE" to "HAVING".

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2006-01-31, 9:23 am

--=_alternative 004E59A485257107_=
Content-Type: text/plain; charset="US-ASCII"

"pedro mpa" <mail.pmpa@sapo.pt> wrote on 01/31/2006 12:49:48 AM:

> Greetings.
>
> I need help on the following query.
> I get an error like "Unknown/Invalid column total_price [...]" when I
try
> filter by total_price.
> How can I do this correctly?
>
> SELECT receipts.*,
> (SELECT SUM(receipt_itens.price) FROM receipt_itens WHERE
> receipt_itens.id_receipt=receipts.id) AS total_price
> FROM receipts
> WHERE
> total_price >= ".$minprice." "
> AND total_price <= ".$maxprice." "
> ORDER BY receipts.date DESC
>
>
> Thanks in advance.
>
> Pedro.
>

You don't need a subquery to get these results. You can also use a regular
low-tech JOIN:

CREATE TEMPORARY TABLE tmpDateTotals SELECT
receipts.date
, sum(receipt_itens.price) total_price
FROM receipts
LEFT JOIN receipt_itens
ON receipts.id = receipt_itens.id_receipt
HAVING total_price >= ".$minprice."
AND total_price <= ".$maxprice."
GROUP BY receipts.date;

SELECT receipts.*, dt.total_price
FROM receipts
INNER JOIN tmpDateTotals dt
ON dt.date = receipts.date
ORDER BY receipts.date desc;

DROP TEMPORARY TABLE tmpDateTotals;

The problem with your original query was that you were trying to compare
the results of a calculation in your WHERE clause. Results do not exist
when WHERE clauses are evaluated but they do by the time the HAVING
clauses are checked. The column`total_price` only exists _after_ the SUM()
function is computed which happens _after_ the restrictions of any ON and
WHERE clauses are applied to your source data. The HAVING clause is
specifically designed to handle this type of comparison. I will bet
dollars to doughnuts that your subquery version of this query will execute
5 to 10 times slower than my version without the subquery. Can you please
try both and report your results?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 004E59A485257107_=--

Sponsored Links