Jump to content

Student Cohort Tracking


NomadicJosh
Go to solution Solved by Barand,

Recommended Posts

I have a small database snippet over at SQLFiddle: http://sqlfiddle.com/#!2/9ff722/55

 

I need help with writing a query that will allow me to track students in a certain cohort from beginning to graduation. I have two tables: application and stu_acad_cred. The application has the start term and the stu_acad_cred are course registrations. If I want to track students in a fall 2012 (12/FA) cohort, then the query must join the two tables; if the student has a start term of 12/FA *and* has registered for 12/FA courses, the student should be counted. I then want to track those student from year to year or term to term to see if the number of students that started in the 12/FA cohort actually decrease overtime. So, the results should return something similar to below.

| # Students (12/FA Cohort) | Year  |

-------------------------------------

|  2                        |  2012 |

|  2                        |  2013 |

|  2                        |  2014 |

I am not sure if this can be done straight forward or with a stored procedure but any help or direction is greatly appreciated.

Link to comment
Share on other sites

Are those the actual results that you expect from the SQLFiddle data?

 

Sorry, that probably was confusing. I was using that as an example of how I want the data to return. Using SQLFiddle to return real data, the result should look like this:

| # Students (12/FA Cohort) | Year  |

-------------------------------------

|  1                        |  2012 |

|  1                        |  2013 |

|  0                        |  2014 |

There is only one student (student #12) in the 12/FA cohort that has registrations for 2012 and 2013. The student does not have registrations for 2014 so it should return 0 or NULL.

Link to comment
Share on other sites

 

Is this what you are looking for?

SELECT 
    YEAR(s.addDate) as yr
  , COUNT(DISTINCT a.personID) as tot
FROM stu_acad_cred s
LEFT JOIN application a
    ON s.stuID = a.personID AND a.startTerm = '12/FA'
GROUP BY yr;

 

Thank you very much @Barand. I can definitely work with that.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.