Check if all the values of a column values are same in a partition by query

Question

I want to find if all the values of a column are same in a partition query.

+----------------------------------------+
|b_name |category|indicator |amount|id   |
+-------|--------|----------|------|-----|
|BUCKET1|CAT1    |Y         | 211.8|21006|
|BUCKET2|CAT1    |N         |275.88|21006|
|BUCKET1|CAT2    |Y         | 265.4|21008|
|BUCKET2|CAT2    |Y         | 289.8|21008|
|BUCKET1|CAT3    |N         | 180.6|21011|
|BUCKET2|CAT3    |N         |180.36|21011|
+----------------------------------------+

I want to get the data based on the indicator column with all the values same for group of category and id columns.

the data with same values of indicator and values with 'Y'

+------------------------------------+
|b_name | category | amount | id     |
+------------------------------------+
|BUCKET1| CAT1     | 211.8  | 21006  |
|BUCKET2| CAT1     | 275.88 | 21006  |
|BUCKET1| CAT3     | 180.6  | 21011  |
|BUCKET2| CAT3     | 180.36 | 21011  |
+------------------------------------+

The data with different values of indicator column and (same values of indicator column with 'N' only)

+------------------------------------+
|b_name | category | amount | id     |
+------------------------------------+
|BUCKET2| CAT2     | 275.88  | 21008  |
+------------------------------------+

Please help me with approach to get the data in the format. I want the correct b_name column values for the corresponding row.

Answer 1

One option uses window functions:

select *
from (
    select t.*,
        min(indicator) over(partition by category, id) min_indicator,
        max(indicator) over(partition by category, id) max_indicator
    from mytable t
) t
where min_indicator = max_indicator

This gives you groups of rows where there is only one distinct indicator value. You can easily adapt this to filter on a given indicator value:

where min_indicator = max_indicator and min_indicator = 'Y'

Or you can filter on groups that have two distinct values:

where min_indicator <> max_indicator 

Answer 2

drop table bucket_data;
create table bucket_data(b_name varchar2(100),category varchar2(100),
indicator VARCHAR2(2),amount number(10),id number(10));

insert into bucket_data values('BUCKET1','CAT1','Y',211.8,21006);
insert into bucket_data values('BUCKET2','CAT1','N',275.88,21006);
insert into bucket_data values('BUCKET1','CAT2','Y',265.4,21008);
insert into bucket_data values('BUCKET2','CAT2','Y',289.8,21008);
insert into bucket_data values('BUCKET1','CAT3','N',180.6,21011);
insert into bucket_data values('BUCKET2','CAT3','N',180.36,21011);

commit;

+------------------------------------+
|b_name | category | amount | id     |
+------------------------------------+
|BUCKET1| CAT1     | 211.8  | 21006  |
|BUCKET2| CAT1     | 275.88 | 21006  |
|BUCKET1| CAT3     | 180.6  | 21011  |
|BUCKET2| CAT3     | 180.36 | 21011  |
+------------------------------------+



 
 with d as(select t.*,count(1) over(partition by CATEGORY,INDICATOR) cnt from bucket_data t
 where INDICATOR <='Y') ,
 d1 as(select t.*,count(1) over(partition by CATEGORY,INDICATOR) cnt from bucket_data t
 where INDICATOR <='N')
 select * from d1,d
 where d1.id<>d.id 
 order by 1 
 fetch first 2 rows only;

Answer 3

For first case, You can use the analytical function as follows:

SELECT * FROM
(SELECT T.*,
       COUNT(1) OVER (PARTITION BY ID) AS TOTAL_COUNT_ID,
       SUM(CASE WHEN INDICATOR = 'Y' THEN 1 ELSE 0 END) AS Y_COUNT
 FROM YOUR_TABLE T) T
 WHERE TOTAL_COUNT_ID > Y_COUNT;