CODEWITHSUNDEEP
mysqlsqlgroup-by
user4281889
user4281889

Reputation: 13

Get Duplicated Count Without Removing

I am trying to get duplicate counts but without actually removing duplicates. I tried using GROUP BY id and then COUNT(id) but it removes all duplicate entries. Is there any way to not remove duplicates?

The table looks like this:

ID1   ID2    Value
1     2      someval
1     3      someval
1     4      someval
2     3      someval
2     1      someval
3     1      someval
4     1      someval

I am trying to get this:

ID1   ID2    Value    COUNT
1     2      someval  3
1     3      someval  3
1     4      someval  3
2     3      someval  2
2     1      someval  2
3     1      someval  1
4     1      someval  1

I used this:

SELECT ID1, ID2, Value, COUNT(ID1) FROM table GROUP BY ID1;

Upvotes: 1

Views: 61

Answers (4)

Rahul Sharma
Rahul Sharma

Reputation: 453

try something like this :

SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

If performance is an issue then an uncorrelated subquery will likely be orders of magnitude faster than a correlated one...

SELECT x.* 
     , cnt 
  FROM my_table x 
  JOIN 
     ( SELECT id1,COUNT(*) cnt FROM my_table GROUP BY id1) y 
    ON y.id1 = x.id1;

Upvotes: 1

Mureinik
Mureinik

Reputation: 311188

One of way doing this is to have a separate query for the count and join on it:

SELECT t.id1, t.id2, t.value, cnt
FROM   my_table t
JOIN   (SELECT   id1, count(*) AS cnt
        FROM     my_table
        GROUP BY id1) c ON t.id1 = c.id1

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can do this with a correlated subquery in MySQL;

select id1, id2, value,
       (select count(*) from table t2 where t2.id1 = t.id1) as count
from table t;

Upvotes: 1

Related Questions