Search code examples
sqldb2subqueryaveragemin

use min() and avg () in subquery

I'm working on db2 from ibm cloud and sql. My data contains 1 table with 3 columns: schools, their overall performance (level) and location. I want to find the location with the least AVERAGE level using a subquery with avg and min.

I have this code which does the job:

select location, avg(level) as avglevel
from schools_table
group by location
order by avglvl
limit 1;

But i'm looking for something more like:

select location
from schools_table
where level = (select min(level) from schools_table);

this yields the minimum of all the values. However i'm interested in the minimum of the average.

please help Many thanks for any insight.

Arturo

Solution

  • You can try the below -

    with cte as
(
select location,avg(safety_score) as safety_score  from chicago_public_schools group by location
)
select community_area_name
from chicago_public_schools
where safety_score = (select min(safety_score) from cte)