MySQL:How to Rewrite a Select element as ‘y’ then using that later in a where statement

So what im trying to do is this but this doesn’t work:

Select count(staffno)as total_staff,avg(salary),branchno
From staff
Where total_staff > 1
Group by branchno

Here is another example:

Select salary,salary*1.015 as proposed_salary
From staff
Where proposed_salary > 50000

Answer

You can’t use an aggregate expression (sum(), count(), …) in the where clause of the query. This clause is evaluated before rows are grouped together by the group by clause, so the aggregates are not yet available.

In SQL, that’s the purpose of the having clause:

select count(staffno) as total_staff, avg(salary), branchno
from staff
having count(staffno) > 1 -- MySQL also allows "total_staff > 1"
group by branchno

---

As for the second query: the alias defined in the select clause is not available in the where clause either (for the same reason as above). You can repeat the expression:

select salary, salary * 1.015 as proposed_salary
from staff
where salary * 1.015 > 50000

Or you can use a derived table (cte or subquery):

select *
from (
    select salary, salary * 1.015 as proposed_salary
    from staff
) t
where proposed_salary > 5000