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?