Count occurrences of distinct values whilst returning the distinct values

Question

Similar to this question here MySQL: Count occurrences of distinct values

However I'd like to return each individual occurrence of the row, with the count for the name. i.e.

expected result:

id         name    count
-----      ------  ------
1          Mark    2
2          Mike    3
3          Paul    1
4          Mike    3
5          Mike    3
6          John    1
7          Mark    2

I can achieve this with a subquery, but my particular example has a complicated main query to filter the results down, and I'd like to avoid having to duplicate that query to achieve correct counts.

score 1 · Answer 1 · answered Dec 19 '17 at 11:09

You could do something like

select a.*,b.cnt
from demo a
join (
  select name,count(*) cnt
  from demo
  group by name 
) b using(name)

In inner query calculate the count for each user and then do a self join with table by matching name and show count from inner query against each record

Demo

Count occurrences of distinct values whilst returning the distinct values

1 Answers1