title: How to filter LINQ query by table column and get count
body: <p>I'm trying to get a list of students based on their status, grouped by their college.</p>
  
  <p>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.</p>
  
  <p><img src="https://i.stack.imgur.com/wdR3R.png" alt="enter image description here"></p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/tF4tq.png" alt="enter image description here"></p>
  
  <p>Accepts = Status('Accepted')
  WebApps = Status('WebApp')
  Total   = Sum(Accpets + WebApps)</p>