title: SQL Server Count and Group-By Specific Question
body: <p>There is a table <code>courses</code> with columns: <code>student</code> and <code>class</code></p>
  
  <p>We need to list out all classes which have more than or equal to 5 students.</p>
  
  <p>For example, the table:</p>
  
  <pre><code>+---------+------------+
  | student | class      |
  +---------+------------+
  | A       | Math       |
  | B       | English    |
  | C       | Math       |
  | D       | Biology    |
  | E       | Math       |
  | F       | Computer   |
  | G       | Math       |
  | H       | Math       |
  | I       | Math       |
  +---------+------------+
  </code></pre>
  
  <p>My answer:</p>
  
  <pre><code>select class from
  (
  select class, count(distinct class) as number 
  from courses
  group by class
  ) as classandnumber
  where number &gt; 5 or number = 5
  </code></pre>
  
  <p>And the correct answer is:</p>
  
  <pre><code>SELECT
      class
  FROM
      (SELECT
          class, COUNT(DISTINCT student) AS num
      FROM
          courses
      GROUP BY class) AS temp_table
  WHERE
      num &gt;= 5
  </code></pre>
  
  <p>And now I have 2 questions:</p>
  
  <ol>
  <li>Why do we need to count student not class? I think my logic is also true: counting the frequency of each class to get how many times this class is chosen, which equals to how many people there are in this class.</li>
  <li>Why distinct is necessary? How can student A takes Math class twice?</li>
  </ol>