CODEWITHSUNDEEP
sqlsql-server-2012
shanavascet
shanavascet

Reputation: 589

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

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.

Upvotes: 3

Views: 61

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

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;

Upvotes: 4

Sushil Soni
Sushil Soni

Reputation: 7

You can achieve the desired result using below query:

SELECT DISTINCT COLUMN1, COLUMN2 FROM TABLE1 WHERE Source <> 'a'

Upvotes: -1

Related Questions