0

I have a table say XYZ, there are three entries for each entity of the column say 'a' and I want to find out if there are more than 3 entries for those entities in the column.

column: 

    a      b      c
    123
    123
    123
    sol
    sol
    sol
    456
    456
    456

Here I want to know if I have more than 3 entries for 123 or sol or 456 in the tab

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
SVC
  • 1
  • Have you tried something straightforward like SELECT COUNT(a) as 'Count-of-A' FROM XYZ GROUP BY a HAVING COUNT(a) = 3 – sll Sep 19 '11 at 09:24

2 Answers2

1

Just try somthing like this, i did not test the sql but you understand the ideea

select a 
from your_table 
where 
   (select count(*) 
    from your_table 
    where a = 'your_entity') > 3
sll
  • 59,352
  • 21
  • 103
  • 153
Dan Bizdadea
  • 1,294
  • 8
  • 13
1

SELECT a, count(a) from YOURTABLE group by a having count(a) > 2
Since you didn't provide a table name i just placed "YOURTABLE" there.

unNamed
  • 919
  • 1
  • 9
  • 17
  • Why `HAVING` instead of `WHERE`? This is a performance killer! – Jon Sep 19 '11 at 09:28
  • AFAIK you can't have COUNT() in a WHERE clause. At least I get an error if I try to. – unNamed Sep 19 '11 at 09:33
  • It's doable the way Dan suggests though. – Jon Sep 19 '11 at 09:39
  • In the end it's the questioner who picks the snippet which fits him best. But thank you for advising us that HAVING is [less performant](http://stackoverflow.com/questions/328636/which-sql-statement-is-faster-having-vs-where). – unNamed Sep 19 '11 at 09:48