Select the difference between 2 column

[ayok]

Hi,

I'm trying to select the difference between column 'original_price' and 'price' which is less than certain amount. Here is my query.

SELECT *,(original_price - price) AS profit FROM items WHERE profit <= '50.00' ORDER BY RAND()

 

It gives me a mysql error that profit is not exist. Would someone tell me how to get expected result?

Thank you.

 

ayok

[requinix]

You can't use aliases in WHERE clauses - pretend that aliasing happens after the conditions.

 

HAVING doesn't have that restriction.

SELECT *, (original_price - price) AS profit FROM items HAVING profit 
 

While I'm at it,

1. If a number is supposed to be a number, don't put quotes around it.

2. Are you sure you want to ORDER BY RAND()? Do you actually want [i]all[/i] of the results in a random order? Or do you just want a couple of them chosen randomly?

[ayok]

You can't use aliases in WHERE clauses - pretend that aliasing happens after the conditions.

 

HAVING doesn't have that restriction.

SELECT *, (original_price - price) AS profit FROM items HAVING profit <= 50.00 ORDER BY RAND()

 

While I'm at it,

1. If a number is supposed to be a number, don't put quotes around it.

2. Are you sure you want to ORDER BY RAND()? Do you actually want all of the results in a random order? Or do you just want a couple of them chosen randomly?

 

Thanks..

I've tried using HAVING, and still got error. Can't I have HAVING and WHERE in a query?

Yes I plan to display those randomly.

[ayok]

Ah.. it works! Thanks!