I'm trying to get a list of students based on their status, grouped by their college.
So I have three tables, Students and Colleges. Each student record has a status, that can be 'Prospect', 'Accepted' or 'WebApp'. What I need to do is get a list of students based on the status selected and then display the College's name, along with the number of students that go to that college and have their status set to the status passed in. I think this needs to be an aggregate query, since the counts are coming from the string Status field.
I'm not sure how to do this in MS SQL, since the count is coming from the same table and it's based on the status field's value.
Here is the start of my query, which takes in the search parameters, but I can't figure out how to filter on the status to return the counts.
SELECT Colleges.Name, [Status], Count([Status]) FROM Students JOIN Colleges ON Students.UniversityId = Colleges.id OR Students.+College = Colleges.Name GROUP BY Students.[Status], Colleges.Name ORDER BY Colleges.Name;
Accepts = Status('Accepted') WebApps = Status('WebApp') Total = Sum(Accpets + WebApps)