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
  • 732,580
  • 175
  • 1,330
  • 1,459
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
  • 61,540
  • 22
  • 104
  • 156
Dan Bizdadea
  • 1,292
  • 8
  • 15
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
  • 969
  • 1
  • 10
  • 18
  • 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