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

sql querry should look like :

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.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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s