MySQL return count of each element AND return all occurrences of that element

Question

I can get a count of the number of times each element occurs using the following MySQL

SELECT COUNT(*) AS count
FROM example
GROUP BY example.value

The problem is, because of the GROUP BY statement, duplicate records will not be returned with their COUNT value.

IE I need this:

But I get this:

Any ideas how this could be done? I am thinking some kind of a join, but I can't figure out the proper way to compare the table to itself

Gurwinder Singh · Accepted Answer · 2017-02-10 19:56:44Z

2

You can use JOIN:

select a.*, b.cnt
from example a
join (
    select value, count(*) cnt
    from example
    group by value
    ) b on a.value = b.value;

answered Feb 9, 2017 at 17:28

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

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

Comments

Sangharsh · Accepted Answer · 2017-02-09 17:28:28Z

2

SELECT e.value, t.count 
FROM example e
LEFT JOIN (SELECT value, COUNT(*) AS count
  FROM example
  GROUP BY example.value) t
ON e.value = t.value;

answered Feb 9, 2017 at 17:28

Sangharsh

3,0292 gold badges17 silver badges28 bronze badges