Group by based on two columns , eliminate data set on another column which is not in group by

Question

What i am looking for is I have a table like this Table 1.

COLUMN1 | COLUMN2 | Source
    --------------- ---------------
     1    |   X   |      a    
     1    |   X  |       b     
     1    |   X   |      c   
     2    |   Y   |      a     
     2    |   Y   |      b     
     3    |   Y   |      c     
     3    |   Y   |      b
     4    |   Y   |      c
     5    |   Z   |      b

Result should be

  COLUMN1 | COLUMN2  
    --------------- 
     3    |   Y 
     4    |   Y  
     5    |   Z 

What i am trying to achieve is I am trying to group the result set based on Column1 and Column2 and if any of the set contains value "a" for the column name "Souce" i need not consider that data set , say

1 X
2 Y

Please advise on this.

Answer 1

You can use conditional aggregation for this:

SELECT column1,
  column2
FROM your_table t
GROUP BY column1,
  column2
HAVING COUNT(CASE WHEN source = 'a' THEN 1 END) = 0;