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

enter image description here

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

enter image description here

  • There are several traps in your "design"? How do you know when an action pass new year? Are the actions always in a sequence, i.e. without any gab? Commented May 4, 2015 at 13:44

1 Answer 1


This is simply an aggregation problem.

  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

  • This query will fail if the table contains other data. I think the provided data are just examples. Commented May 4, 2015 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.