Return registers based on its score average

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

View on DB Fiddle

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?

Solution

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