SQL query or sub-query?

Question

I've got a table of student information in MySQL that looks like this (simplified):

|   age : int   |   city : text   |   name : text   |
-----------------------------------------------------
|               |                 |                 |

I wish to select all student names and ages within a given city, and also, per student, how many other students in his age group (that is, how many students share his age value).

I managed to do this with a sub-query; something like:

select 
   name, 
   age as a, 
   (select 
       count(age) 
    from 
       tbl_students 
    where 
       age == a) 
from 
   tbl_students 
where 
   city = 'ny'

But it seems a bit slow, and I'm no SQL-wiz, so I figure I'd ask if there's a smarter way of doing this. The table is indexed by age and city.

Answer 1

select 
    t1.name, 
    t1.age as a, 
    count(t2.age) NumberSameAge
from 
   tbl_students t1 inner join tbl_students t2
    on t1.age=t2.age
where 
   city = 'ny'
group by t1.name, t1.age

not tested, but something like that. I.o.w. a groupby on a join. This sometimes can be faster as the query you're running is doing a nested subquery for every row returned, and the query I posted above (or at least, the structure with a join and a groupby) performs a query on the related students just once.

Answer 2

It might be easier to grab a sub-query that grabs everything at once (vs. 1000 rows where it runs the sub-query 1000 times).

SELECT Age, count(*) AS SameAge FROM tbl_students

Making the full query:

SELECT t.Name, t.Age, s.SameAge
FROM tbl_students t
  INNER JOIN (
    SELECT Age, count(*) AS SameAge FROM tbl_students
  ) AS s
    ON (t.Age = s.Age) -- m:1
WHERE t.City = 'NY'