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

Question

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?

Answer 1

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

Answer 2

select s.id, s.age, c.count
from students s
inner join (
    select age, count(*) as count
    from students
    group by age
) c on s.age = c.age
order by id

Answer 3

I would do something like:

select
 A.id, A.age, B.count 
from 
 students A, 
 (select age, count(*) as count from sudents group by age) B
where A.age=B.age;

Answer 4

If you're using Oracle, then a feature called analytics will do the trick. It looks like this:

select id, age, count(*) over (partition by age) from students;

If you aren't using Oracle, then you'll need to join back to the counts:

select a.id, a.age, b.age_count
  from students a
  join (select age, count(*) as age_count
          from students
         group by age) b
    on a.age = b.age