Compare two Count() values sql

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;

Answer

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);