"pedro mpa" <[EMAIL PROTECTED]> 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
