How to count occurrences of a column value efficiently in SQL?

performance, sql

Solution

This should work:

SELECT age, count(age) 
  FROM Students 
 GROUP by age

If you need the id as well you could include the above as a sub query like so:

SELECT S.id, S.age, C.cnt
  FROM Students  S
       INNER JOIN (SELECT age, count(age) as cnt
                     FROM Students 
                    GROUP BY age) C ON S.age = C.age

Problem

I have a table of students: ``` id | age -------- 0 | 25 1 | 25 2 | 23 ``` I want to query for all students, and an additional column that counts how many students are of the same age: ``` id | age | count ---------------- 0 | 25 | 2 1 | 25 | 2 2 | 23 | 1 ``` What's the most efficient way of doing this? I fear that a sub-query will be slow, and I'm wondering if there's a better way. Is there?

Original source