CODEWITHSUNDEEP
mysqlsql
ConnorP
ConnorP

Reputation: 107

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

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:

  1. apples - 2
  2. apples - 2
  3. oranges - 1
  4. bananas - 3
  5. bananas - 3
  6. bananas - 3

But I get this:

  1. apples - 2
  2. oranges - 1
  3. bananas - 3

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

Upvotes: 0

Views: 361

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

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;

Upvotes: 2

Sangharsh
Sangharsh

Reputation: 3019

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;

Upvotes: 2

Related Questions