CODEWITHSUNDEEP
mysqlsql
olym
olym

Reputation: 3

MySQL count occurrences but return all rows

Im trying to count occurrences of name, but i want each row returned no matter if that name has already been counted. The data looks like;

ID |  NAME  
1     Peter 
2     Simone 
3     Otto
4     Cedric 
5     Peter 
6     Cedric
7     Cedric

The following only returns one row per unique name 

select id, first_name, count(first_name)from table group by first_name

ID |  FIRST_NAME  | count(first_name)
1     Peter          2
2     Simone         1
3     Otto           1
4     Cedric         3

But im trying to return every row, something like

ID |  FIRST_NAME  | count(first_name)
1     Peter          2
2     Simone         1
3     Otto           1
4     Cedric         3
5     Peter          2
6     Cedric         3
7     Cedric         3

Upvotes: 0

Views: 160

Answers (3)

fancyPants
fancyPants

Reputation: 51888

Edit: now that I've seen the other answers, why is joining better than using a correlated subquery? Because a correlated subquery is executed for every row in your table. When you join it, the query is executed just once.

Then you have to join those queries.

select * from
table 
inner join (
    select first_name, count(first_name) as name_count from table group by first_name
) qcounts on table.first_name = qcounts.first_name

Also note, that in your query you have to remove id from the select clause, since you neither have it in your group by clause nor do you apply an aggregate function on it. Therefore a random row is returned for this column.
It's a good idea to let MySQL remind you of that by activating the only_full_group_by sql mode. To do this you can do 

set global sql_mode = concat(@@global.sql_mode, 'only_full_group_by');

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You can use a Correlated subquery:

SELECT t1.id, 
       t1.first_name,
       (SELECT COUNT(id) 
        FROM table t2 
        WHERE t2.first_name = t1.first_name) AS total_count
FROM table t1

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37377

If you are using MySQL version >= 8.0, then you can use window functions:

select id,
       first_name,
       count(*) over (partition by first_name)
from table

For earlier versions:

select id,
       first_name,
       (select count(*) from table where first_name = t.first_name)
from table t

Upvotes: 2

Related Questions