Count Duplicates with same id passing in one coulmn

Question

Hi there m trying to calculate the row count for same value,

id,value
1 | a
2 | b
3 | c
4 | d
5 | e

and my query is

select value, count(*) as Count from mytable  where id in('4','2','4','1','4') group by value having count(*) > 1

for which my expected output will be,

value,Count
 d |  3
 b |  1
 a |  1

Thanks, any help will be appreciated

score 0 · Answer 1 · answered Jan 30 '13 at 08:16

0

Try that:

  SELECT value, count(value) AS Count 
  FROM mytable m
    WHERE value = m.value
    GROUP BY value

answered Jan 30 '13 at 08:16

Borys

2,676
2
24
37

thanks , but its not woking when i am passind the id value to it – Afsar Jan 30 '13 at 08:20
Are you sure you've changed all occurrences of value with id? It's really not many things that can fail in here... Or maybe your id is unique and this is where the confusion comes from? – Borys Jan 30 '13 at 08:25
yes my id is unique and when i query the same it some specific time it should have to show the specific count , hope u can understand – Afsar Jan 30 '13 at 08:36
unfortunately I'm not sure what you mean – Borys Jan 30 '13 at 08:49

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

SELECT t.id, t.value, COUNT(t.id) 
FROM 
  test t
  JOIN 
    ( SELECT 1 AS id
      UNION ALL SELECT 3
      UNION ALL SELECT 3
      UNION ALL SELECT 4
      UNION ALL SELECT 1
      UNION ALL SELECT 1 ) AS tmp
    ON t.id = tmp.id
GROUP BY t.id

Sample on sqlfiddle.com

See also: Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?

Of course, your IN parameter will be dynamic, and thus you will have to generate the corresponding SQL statement for the tmp table.

That's the SQL-only way to do it. Another possibility is to have the query like you have it in your question and afterwards programmatically associate the rows to the count passed to the IN parameter.

edited May 23 '17 at 12:27

Community

1
1

answered Jan 30 '13 at 08:25

Vincent Mimoun-Prat

28,208
16
81
124

thanks Marvin , but here my ID values will be dynamic in range , it changes accordingly , so how can i pass the id range in query , – Afsar Jan 30 '13 at 08:31
I think you'll have to generate the inner SELECT/UNION clause from your dynamic values, I don't really see another way to do it (or do it programmatically after the result comes back, see edited answer) – Vincent Mimoun-Prat Jan 30 '13 at 08:36

Count Duplicates with same id passing in one coulmn

2 Answers2