Syzorr Syzorr - 7 months ago 17
SQL Question

Returning the Min() of a Count()

I am studying for an SQL test and the previous year has the final question:


Name the student who has studied the least number of papers. How many
papers have they studied?


So far, this is the select query that I have created:

select min(Full_Name), min(Amount)
from (select st.ST_F_Name & ' ' & st.ST_L_Name as Full_Name, count(*) as Amount
from (student_course as sc
inner join students as st
on st.ST_ID=sc.SC_ST_ID)
group by st.ST_F_Name & ' ' & st.ST_L_Name)


This works perfectly for returning the result I want but I'm not sure if this is the way I should be doing this query? I feel like calling min() on the Full_Name could potentially backfire on me under certain circumstances. Is there a better way to be doing this? (this is in MS Access for unknown reasons)

Answer

If you want only 1 of such students if there are multiple, this is probably the simplest:

select st.ST_F_Name, st.ST_L_Name, count(*) as Amount
from student_course as sc
inner join students as st
on st.ST_ID=sc.SC_ST_ID
group by st.ST_ID
order by Amount ASC LIMIT 1

However, if you want to find all stuch students, you follow a different approach. We use a WITH clause to simplify things, that defines a CTE (Common Table Expression) computing the number of courses per-student. And then we select students where their number equals to the minimum in that CTE:

with per_student as (
select st.ST_F_Name, st.ST_L_Name, count(*) as Amount
from student_course as sc
inner join students as st
on st.ST_ID=sc.SC_ST_ID
group by st.ST_ID
)
select * from per_student 
where amount = (select min(amount) from per_student)

But the real trick in that question is that there might be students that didn't take ANY courses. But with approaches presented so far you'll never see them. You want something like this:

with per_student as (
select st.ST_F_Name, st.ST_L_Name, count(sc.SC_ST_ID) as Amount
from student_course as sc
right outer join students as st
on st.ST_ID=sc.SC_ST_ID
group by st.ST_ID
)
select * from per_student 
where amount = (select min(amount) from per_student)