How do I use a calculated column in a WHERE clause?

I'm trying to figure out the total VOLUME of certain products in my database, I'm trying to list all products which have a volume of 3000 or more....

So I do a query like so:

SELECT (
products_width * products_height * products_length
) AS total_volume
FROM `price`
where total_volume > 3000

But I get an error stating that the column "total_volume" does not ex开发者_JS百科ist in the where clause, can you let me know how to solve this?

try :

SELECT (
products_width * products_height * products_length
) AS total_volume
FROM `price`
having total_volume > 3000

or

select *
from (
    SELECT (
        products_width * products_height * products_length
    ) AS total_volume
    FROM `price`
)
where total_volume > 3000

For the explanation, you can't use total_volume in the where clause directly, because it is created in the selection part of the query. having can be used in this case, or you can use a subquery.

You need to repeat the formula in your WHERE:

SELECT (products_width * products_height * products_length) AS total_volume
    FROM `price`
    where (products_width * products_height * products_length) > 3000

SELECT (
products_width * products_height * products_length
) AS total_volume
FROM `price`
HAVING total_volume >= 3000

that is - change WHERE to HAVING, that's all.