table tb_students is following https://www.db-fiddle.com/f/ukpJEHGLE5sKkHuqNjFKPZ/5
| id | first_name | last_name | test_name | score |
|---|---|---|---|---|
| 1 | A | Smith | History | 0.8 |
| 2 | F | Snow | Math | 0.5 |
| 3 | F | Snow | History | 0.5 |
| 4 | A | Smith | English | 1.0 |
| 5 | C | Bayer | English | 1.5 |
| 6 | F | Snow | English | 1.5 |
I need to return all students whose avg test score on humanities test ("English" and "History) is 1.0 or higher
select first_name, last_name, round(avg(score),1) as avg_score
from pruebas.tb_students
where test_name in ('History', 'English')
group by first_name, last_name
It throws an error when try to include in a WHERE statement avg_score > 1.0
Do I need implement some kind of subquery?
CodePudding user response：
You can't refer to a column alias in the where or having clause since they are evaluated before the select (but yes you could if you used a derived table with an outer select);
This is a common question.
You can simply repeat the expression using a having clause for filtering (the optimizer will only evaluate it once) :
having round(avg(score),1) >= 1;
>= since your question states is 1.0 or higher