Get list of students having actions in Oracle I have a table of student actions (id, student_id, type, and quarter). I need to get a list of all the students, along w/ a flag of whether they had a particular action in the current quarter, and the past quarter but I'm not sure how to do this in Oracle SQL.

student_actions table



Assuming that the current quarter is 3, and that I'm only interested in the Expulsion and Probation actions how would I get a result set like the one I've mocked up below?

expected results
This is simply an aggregation problem.

select
  student_id,
  count(case when type in ('Expulsion','Probation') and quarter=3-1 then 1 end) as previous,
  count(case when type in ('Expulsion','Probation') and quarter=3 then 1 end) as "CURRENT"
from student_actions
group by student_id;


See http://sqlfiddle.com/#!4/f4263/10/0