1

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

1
  • 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? May 4 '15 at 13:44
5
+100

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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