CODEWITHSUNDEEP
phpmysqllinuxdatabase
bmacuer
bmacuer

Reputation: 37

Unknown column MYSQL

I'm trying to get a defined number of product that have been sold at least 6 times, but mysql is giving me an unknown column but it is ordering by the same value if the value is taking out of the WHERE clause.

this is my query.

SELECT 
ps.product_id, 
(SELECT IFNULL(SUM(s.product_quantity),0) FROM product_stock s WHERE ps.product_id = s.product_id AND s.product_quantity > 0) AS stock, 
SUM(ps.product_quantity) AS vendidos 
FROM product_sold ps 
WHERE 
ps.product_sold_approved_time >= 1419786999
AND
vendidos >= 6
GROUP BY ps.product_id 
ORDER BY vendidos DESC

Upvotes: 0

Views: 48

Answers (2)

Abhishekh Gupta
Abhishekh Gupta

Reputation: 6236

You are getting this as you are using alias in where clause. The order of execution of clause is like this:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

You are confused here that the calculated SELECT columns will be available in the WHERE clause but this is not the case. Because the SELECT clause executed right before hand, everything from the SELECT should be available at the time of ORDER BY execution.

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can not use the column alias name in the where condition. You can move it as having clause

SELECT 
ps.product_id, 
(SELECT IFNULL(SUM(s.product_quantity),0) FROM product_stock s WHERE ps.product_id = s.product_id AND s.product_quantity > 0) AS stock, 
SUM(ps.product_quantity) AS vendidos 
FROM product_sold ps 
WHERE 
ps.product_sold_approved_time >= 1419786999
GROUP BY ps.product_id 
having vendidos >= 6 
ORDER BY vendidos DESC

Upvotes: 2

Related Questions