Compare two Count() values sql-CodePudding

I have a table called "Takes" that has the following information. Takes(stud_id, class_id, sec_id, semester, year, grade)

I wanted to find the id's of the students who took more classes in 2009 than in 2010 (in terms of number of classes. I have used the following code to extract the 2009 students total number of classes but not sure how to do the comparison

select stud_id, count(class_id) from Takes where year = 2009 group by stud_id;

CodePudding user response：

You can use conditional aggregation (CASE WHEN inside an aggregation function) and a HAVING clause:

select
  stud_id,
  count(case when year = 2009 then 1 end) as classes_in_2009,
  count(case when year = 2010 then 1 end) as classes_in_2010
from takes 
group by stud_id
having count(case when year = 2009 then 1 end) >
       count(case when year = 2010 then 1 end);

CodePudding user response：

this is not the most efficient query, but u can try this:

SELECT aa.Stud_id
FROM (  SELECT Stud_id, Year, COUNT(Class_id) Class_id
        FROM Takes
    GROUP BY Stud_id, Year) aa
    WHERE aa.Year = 2009
    AND aa.Class_id > (SELECT bb.Class_id
                        FROM (  SELECT Stud_id, Year, COUNT(Class_id) Class_id
                                  FROM Takes
                              GROUP BY Stud_id, Year) bb
                       WHERE aa.Stud_id = bb.Stud_id AND bb.Year = 2010);

This gets more effient by applying the WHERE clause right away and using HAVING for the comparision of the counts:

SELECT aa.Stud_id
FROM Takes aa
WHERE aa.Year = 2009
GROUP BY aa.Stud_id
HAVING COUNT(*) > 
(
  SELECT COUNT(*)
  FROM Takes bb
  WHERE bb.Stud_id = aa.Stud_id
  AND bb.Year = 2010
);