How to count occurrences of a column value efficiently in SQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to performance