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

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?

7 Answers

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 

Here's another solution. this one uses very simple syntax. The first example of the accepted solution did not work on older versions of Microsoft SQL (i.e 2000)

SELECT age, count(*) FROM Students GROUP by age ORDER BY age 

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 

I would do something like:

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

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 

and if data in "age" column has similar records (i.e. many people are 25 years old, many others are 32 and so on), it causes confusion in aligning right count to each student. in order to avoid it, I joined the tables on student ID as well.

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

This should work:

You can fetch all row your table and count the row.

select *,count(*) over() from students;