CODEWITHSUNDEEP
oracle-database
Compassionate
Compassionate

Reputation: 1

Check all values of a column in a group to be same

I have the below set of values in a result set,

Col1    Col2    Col3    Col4    Col5
M         CP    C        NO     T1
M         CP    C        NO     T2
M         CP    C        NO     T3
M         CP    A        YES    T4
M         CP    A        MAY    T5
M         CP    A        NO     T6
M         CP    D        WHY    T7
M         CP    E         NO    T8

Query) I would like to get values of Col5 where all values of Col4=NO after grouping on Col1,Col2 and Col3 . How to achive this using a single query in ORACLE.

E.g: this condition is satisfied only for the group ( M CP C to get values T1,T2,T3) and the group ( M CP E - to get the value of T8)

I mean in a group all rows should have the value "NO" for Col4.

Please suggest.

Upvotes: 0

Views: 1861

Answers (3)

Rusty
Rusty

Reputation: 2138

Query with test data:

with data as(
select 'M'  as col1, 'CP' as col2, 'C'  as col3, 'NO' as col4, 'T1' as col5 from dual
 union all
select 'M',          'CP',         'C',          'NO',         'T2'         from dual
 union all
select 'M',          'CP',         'C',          'NO',         'T3'         from dual
 union all
select 'M',          'CP',         'A',          'YES',        'T4'         from dual
 union all
select 'M',          'CP',         'A',          'MAY',        'T5'         from dual
 union all
select 'M',          'CP',         'A',          'NO',         'T6'         from dual
 union all
select 'M',          'CP',         'D',          'WHY',        'T7'         from dual
 union all
select 'M',          'CP',         'E',          'NO',         'T8'         from dual)
select col1, col2, col3, max(col4), max(col5)  
  from data
  group by col1, col2, col3
  having max(col4) = min(col4)
     and max(col4) = 'NO';

Output:

COL1    COL2    COL3    MAX(COL4)   MAX(COL5)
M   CP  E   NO  T8
M   CP  C   NO  T3

Please bear in mind that we assume that cik5 has only single value for col4 ='NO' within a group. Think about what query should return if we add following row:

select 'M',          'CP',         'E',          'NO',         'T9'         from dual

Upvotes: 0

Jose Rui Santos
Jose Rui Santos

Reputation: 15319

This query selects all groups that have Col4 = 'NO':

select Col1, Col2, Col3
  from table1
 group by Col1, Col2, Col3     -- I want to group by Col1, Col2, Col3 that have
having min(Col4) = max(Col4)   -- a Col4 with same value
   and min(Col4) = 'NO';       -- and that value is NO

Col1    Col2    Col3
--------------------
M       CP      C
M       CP      E

Note that the use of min=max is useful to determine whether the column has always the same value, in this case 'NO'.

Now, the final query is to select all Col5 from those two groups, plus the Col4='NO':

select Col5
  from table1
 where (col1, Col2, Col3) in (

    select Col1, Col2, Col3
      from table1
     group by Col1, Col2, Col3
     having min(Col4) = max(Col4)
        and min(Col4) = 'NO'
 );

which outputs the result you are looking for:

Col5
-------
T1
T2
T3
T8

Upvotes: 1

DirkNM
DirkNM

Reputation: 2664

If I understand you correctly, this query may help:

SELECT Col1, Col2, Col3, Col4, Col5
  FROM(SELECT Col1, Col2, Col3, Col4, Col5,
              COUNT(*) OVER (PARTITION BY Col1, Col2, Col3) AS cnt1,
              COUNT(*) OVER (PARTITION BY Col1, Col2, Col3, Col4) AS cnt2 
         FROM your_table
      )
 WHERE Col4 = 'NO'
   AND cnt1 = cnt2;

Upvotes: 1

Related Questions