mysql – Questions for having clause and where clause

05/02/2018


I have a very simple question. I am using Mysql bench, and i had a data which likes below:

dateordered_new        orderstatus  orders
2016-06-23 23:19:23     returned       8
2016-06-01 23:19:23     completed     12
2016-06-22 23:19:23     returned       9
2016-06-04 23:19:23     completed     27
...etc...

The question is simple, I want to show the amount of orders which has been returned in each month.

And here is my query:

select month(dateorderednew) as Month, sum(orders) as return_orders
from table_a
group by month
having orderstatus='returned;

Considering the difference between where clause and having clause, my syntax should be worked. However, the system told me that “Error Code: 1054. Unknown column ‘orderstatus’ in ‘having clause'” And it was wired.

However, when I modified my query like this:

select month(dateorderednew) as Month, sum(orders) as return_orders
from table_a
where orderstatus='returned
group by month;

And it worked.

So, it was really confusing. I think having clause should follow by the group by statement. But I cannot answer why this case happened?

Do you guys have any idea for this?

قالب وردپرس