SQL query or subquery?
I have a student information table in MySQL that looks like this (simplified):
| age : int | city : text | name : text |
-----------------------------------------------------
| | | |
I want to select all names and age names of students in a given city, and for each student, how many other students are in his age group (that is, how many students share his age value).
I managed to do it using a subquery; something like:
select
name,
age as a,
(select
count(age)
from
tbl_students
where
age == a)
from
tbl_students
where
city = 'ny'
But this seems a little slow and I am not SQL-wiz, so I think I will ask if there is a smarter way to do this. The table is indexed by age and city.
source to share
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
untested, but something like that. Iow group by connection. This can sometimes be faster, since the query being executed executes a nested subquery for each row returned, and the query I posted above (or at least a structure with a join and a group) does the query to the related students just once.
source to share
It might be easier to grab a subquery that grabs everything at once (versus 1000 rows where it executes the subquery 1000 times).
SELECT Age, count(*) AS SameAge FROM tbl_students
Executing a 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'
source to share