title: Multiple Select against one CTE
body: <p>I have a CTE query filtering a table Student</p>
  
  <pre><code>  Student
  
     (
      StudentId PK,
      FirstName ,
      LastName,
      GenderId,
      ExperienceId,
      NationalityId,
      CityId
    )
  </code></pre>
  
  <p>Based on a lot filters (multiple cities, gender, multiple experiences (1, 2, 3), multiple nationalites), I create a CTE by using dynamic sql and joining the student table with a user defined tables (<code>CityTable, NationalityTable,...</code>)</p>
  
  <p>After that I have to retrieve the count of student by each filter like</p>
  
  <pre><code>CityId City Count
  
  NationalityId Nationality Count
  </code></pre>
  
  <p>Same thing the other filter.</p>
  
  <p>Can I do something like</p>
  
  <pre><code>  ;With CTE(
           Select
           FROM Student
           Inner JOIN ...
           INNER JOIN ....)
    SELECT CityId,City,Count(studentId)
    FROm CTE
    GROUP BY CityId,City
  
    SELECT GenderId,Gender,Count
    FROM CTE
    GROUP BY  GenderId,Gender
  </code></pre>
  
  <p>I want to something like what LinkedIn is doing with search(people search,job search)</p>
  
  <p><a href="http://www.linkedin.com/search/fpsearch?type=people&amp;keywords=sales+manager&amp;pplSearchOrigin=GLHD&amp;pageKey=member-home" rel="noreferrer">http://www.linkedin.com/search/fpsearch?type=people&amp;keywords=sales+manager&amp;pplSearchOrigin=GLHD&amp;pageKey=member-home</a></p>
  
  <p>It's so fast and do the same thing.</p>