CODEWITHSUNDEEP
phpmysql
Kodr.F
Kodr.F

Reputation: 14370

php mysql SELECT AS with WHERE Column not found?

i am trying to use WHERE condition in mysql PHP PDO from SELECT AS , i got error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'total_consumers' in 'where clause'null

my query :

SELECT category.* , SUM(Consumer.capacity) AS total_consumers
FROM company AS company
RIGHT JOIN company AS Consumer ON ( Consumer.category_id = company.category_id AND Consumer.company_type = 'Consumer'  )
RIGHT JOIN category AS category ON ( category.category_id = company.category_id  )
WHERE total_consumers > 0 
GROUP BY category.category_title

target :

i want to get all records inc category table , and they should be exists in company table as consumer and producer , if consumer null don't select it

here is the json result of above query

if i remove the WHERE condition i got the below JSON response

http://json.live/166EaR

as u can see some records has total_consumers : null that should not be selected

any idea how to do my point : ( why i can't use SELECT AS in WHERE statement ) 

WHERE total_consumers >  
or
WHERE total_consumers != null
or

WHERE xx NOT NULL

Upvotes: 1

Views: 1005

Answers (1)

Jens
Jens

Reputation: 69440

You can not use an alias from select in the where clause. You have to use the having clause:

SELECT category.* , SUM(Consumer.capacity) AS total_consumers
FROM company AS company
RIGHT JOIN company AS Consumer ON ( Consumer.category_id = company.category_id AND Consumer.company_type = 'Consumer'  )
RIGHT JOIN category AS category ON ( category.category_id = company.category_id  )
GROUP BY category.category_title
having total_consumers > 0

Upvotes: 5

Related Questions