MySQL: Count occurrences of distinct values for each row

Question

Based on an example already given, I would like to ask my further question. MySQL: Count occurrences of distinct values

example db

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

expected result

name       count
-----      -----
Mark       2
Mike       3
Paul       1
Mike       3
Mike       3
John       1
Mark       2

In my opinion 'GROUP BY' doesn't help. Thank you very much.

Possible duplicate of [MySQL: Count occurrences of distinct values](https://stackoverflow.com/questions/1346345/mysql-count-occurrences-of-distinct-values) — WhatsThePoint, Nov 01 '18 at 14:45
@WhatsThePoint it is not duplicate. Please check the expected output is different. — Madhur Bhaiya, Nov 01 '18 at 14:48

Madhur Bhaiya · Accepted Answer · 2018-11-01T14:50:04.537

Simplest approach would be using Count() as Window Function over a partition of name; but they are available only in MySQL 8.0.2 and onwards.

However, another approach is possible using a Derived Table. In a sub-select query (Derived Table), we will identify the counts for each unique name. Now, we simply need to join this to the main table, to show counts against each name (while not doing a grouping on them):

SELECT 
  t1.name, 
  dt.total_count 
FROM your_table AS t1 
JOIN 
(
 SELECT name, 
        COUNT(*) AS total_count 
 FROM your_table
 GROUP BY name
) AS dt ON dt.name = t1.name 
ORDER BY t1.id

If MySQL 8.0.2+ is available, the solution would be less verbose:

SELECT 
  name, 
  COUNT(*) OVER (PARTITION BY name) AS total_count 
FROM your_table

MySQL: Count occurrences of distinct values for each row

1 Answers1

Linked