php mysql SELECT AS with WHERE Column not found?

Question

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

Jens · Accepted Answer · 2016-03-02 14:07:32Z

5

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

edited Mar 2, 2016 at 14:07

answered Mar 2, 2016 at 14:02

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MatsLindh Over a year ago

Just to clarify - you can use the alias, but not from an aggregate column. The WHERE limits the rows considered for aggregation, while the HAVING clause is applied after aggregation has happened.

Kodr.F Over a year ago

i did the above , i got error for first one

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY category.category_title' at line 6null

and for second one Error : SQLSTATE[HY000]: General error: 1111 Invalid use of group functionnull

Kodr.F Over a year ago

adding having total_consumers > 0 after group fixed it , many thanks u made my day

Collectives™ on Stack Overflow

php mysql SELECT AS with WHERE Column not found?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related