1

I have a table of students:

id | age|num
-------------
0  | 25| 10
1  | 25| 5
2  | 23| 5

I want to query for all students, and an additional column that counts how many students are of the same age:

id | num | age | agecount|numcount
-------------------------------------
0  | 10 | 25  | 2       |1
1  | 5  | 23  | 1       |2

What's the most efficient way of doing this? if there's a better way**. Is there?

CSK
  • 777
  • 7
  • 17

2 Answers2

2

You have two queries:

One for the list of the students:

SELECT
    id, age, num
FROM
   students

And one for the count of students with the same age:

SELECT
   age
   , count(1)
FROM
   students
GROUP BY
   age

Now you have to combine these two queries: You can JOIN one or more tables or subqueries. Lets do it:

SELECT
    S.id, S.age, S.num, age.cnt
FROM
    -- List of all students
    (
        SELECT
            id, age, num
        FROM
           students
    ) S
    -- Ages with student counts
    INNER JOIN (
        SELECT
            age
            , count(1) AS cnt
       FROM
           students
       GROUP BY
           age
    ) A
        ON S.age = A.age

You can simplify the above query with removing the first subquery and use the students table instead:

SELECT
    S.id, S.age, S.num, A.cnt
FROM
    students S
    -- Ages with student counts
    INNER JOIN (
        SELECT
            age
            , count(1) AS cnt
       FROM
           students
       GROUP BY
           age
    ) A
        ON students.age = age.age

Now you can modify this sample query to achieve your goal.

Pred
  • 8,789
  • 3
  • 26
  • 46
  • Just for my information : Is it necessary to join when you could just do : SELECT id, age, num, (select count(1) from students t2 where t2.age = t1.age) as cnt ? Isn't join a bit slower ? – Logar Aug 28 '14 at 08:53
  • 1
    It depends. You can use a single value subquery in the select list, but it will be evaulated and will run for each and every row. With joining the joined resultset will be calculated and will be joined to all related records. You have to test it by yourself in your current table. With small tables the difference will be small, but in big databases, the difference could be huge. You have to test both of them. – Pred Aug 28 '14 at 08:58
  • count of two fields(age and num) – CSK Aug 28 '14 at 08:59
0

To count student with the same age :

select age ,count(age) from s_table group by age
G.Nader
  • 847
  • 7
  • 9