selecting multiple records where count is some value

Question

There is a huge database with more than 500k values, but with only one table having all the data. I need to extract some of it for a given condition.

Table structure is like this,

column_a | column_b    
A        | 30    
A        | 40    
A        | 70    
B        | 25    
B        | 45    
C        | 10    
C        | 15    
C        | 25

I need to extract all the data having a count(column_a) = 3. the catch is that i need to get all the three records too. Like this,

column_a | column_b 
A        | 30    
A        | 40    
A        | 70    
C        | 10    
C        | 15    
C        | 25

I have tried to do this with a query like this

select column_a,column_b group by column_a having count(*)=3;

Here i get the correct values for column_a but only one record from each.

Thanks in advance, Bhashithe

Answer 1

One approach is to INNER JOIN your original table to a subquery which identifies the column_a records which come in groups of exactly 3.

SELECT t1.column_a, t1.column_b
FROM table t1
INNER JOIN
(
    SELECT column_a, COUNT(*)
    FROM table
    GROUP BY column_a
    HAVING COUNT(*) = 3
) t2
ON t1.column_a = t2.column_a