MySQL count occurrences but return all rows

Question

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

Michał Turczyn · Accepted Answer · 2018-08-26 09:20:01Z

2

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

edited Aug 26, 2018 at 9:20

answered Aug 26, 2018 at 7:29

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Strawberry Over a year ago

Id seems rather meaningless in this context

Michał Turczyn Over a year ago

@Strawberry OP included it in resultset, so who I am to change those requirements :)

olym Over a year ago

i included "id" simply as an example as it was easier rather than real column name/data. so thanks for including in answer @MichalTurczyn, worked exactly as needed.

Michał Turczyn Over a year ago

@olym If my answer has helped you,you should accept it (green check mark on the left) and optionally upvote.

olym Over a year ago

@MichałTurczyn im unable to upvote or accept answer due to being a newbie. Your solution is the easiest for my situation, however as fancyPants noted below, a correlated subquery might take a performance hit

Madhur Bhaiya · Accepted Answer · 2018-08-26 10:00:33Z

1

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

edited Aug 26, 2018 at 10:00

answered Aug 26, 2018 at 7:16

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Comments

fancyPants · Accepted Answer · 2018-08-26 13:09:51Z

1

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');

edited Aug 26, 2018 at 13:09

answered Aug 26, 2018 at 7:15

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

1 Comment

Nick Over a year ago

I think you mean table.first_name = qcounts.first_name

Collectives™ on Stack Overflow

MySQL count occurrences but return all rows

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related