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

SqlPerformance

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

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

Solution 2 - Sql

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

Solution 3 - Sql

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

Solution 4 - Sql

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;

Solution 5 - Sql

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

Solution 6 - Sql

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*

Solution 7 - Sql

This should work:

You can fetch all row your table and count the row.

select *,count(*) over() from students;

Categories
Recommended Sql Solutions
Recommended Performance Solutions

Previous Solution:

Are fluid websites worth making anymore?

HtmlCssLayoutStaticFluid Layout

Next Solution:

How to call erase with a reverse iterator

C++

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionAssaf LavieView Question on Stackoverflow
Solution 1 - SqlMike DinescuView Answer on Stackoverflow
Solution 2 - SqlDamianView Answer on Stackoverflow
Solution 3 - SqlJeremy BourqueView Answer on Stackoverflow
Solution 4 - SqlquosooView Answer on Stackoverflow
Solution 5 - SqlD'Arcy RittichView Answer on Stackoverflow
Solution 6 - Sqlafii_palangView Answer on Stackoverflow
Solution 7 - SqlAkhil ZadeView Answer on Stackoverflow