I'm having trouble trying to come up with a query that retrieves COLUMN_A where:

• COLUMN_C is having count > 1

• And COLUMN_B's values are different.

Table contents

COLUMN_A                                COLUMN_B                COLUMN_C
--------------------------------        ----------------        ---------
000111222                               AAAAAAAAA               100
000111222                               BBBBBBBBB               100
000111222                               CCCCCCCCC               300
000111222                               CCCCCCCCC               300

000888999                               DDDDDDDDD               300
000888999                               DDDDDDDDD               300

777666555                               EEEEEEEEE               100
777666555                               FFFFFFFFF               100

234567890                               GGGGGGGGG               100
234567890                               GGGGGGGGG               100

444333111                               HHHHHHHHH               100

Expected result

COLUMN_A                                COLUMN_B                COLUMN_C
--------------------------------        ----------------        ---------
000111222                               AAAAAAAAA               100
000111222                               BBBBBBBBB               100

777666555                               EEEEEEEEE               100
777666555                               FFFFFFFFF               100

This is where I'm stuck at:

select COLUMN_A, count(*) from SOME_TABLE where COLUMN_C = '100' group by COLUMN having count(*) > '1';

But this query retrieves:

COLUMN_A                                COLUMN_B                COLUMN_C
--------------------------------        ----------------        ---------
000111222                               AAAAAAAAA               100
000111222                               BBBBBBBBB               100

777666555                               EEEEEEEEE               100
777666555                               FFFFFFFFF               100

234567890                               GGGGGGGGG               100
234567890                               GGGGGGGGG               100

I strongly suspect the query lacks a distinct or a count somewhere but, for the life of me, nothing comes to mind.

Any info would be greatly appreciated.

CodePudding user response：

We can try to use two count window functions in the subquery for your grouping logic.

SELECT t1.COLUMN_A ,t1.COLUMN_B ,t1.COLUMN_C
FROM (
    select t1.*, 
           count(*) OVER(PARTITION BY COLUMN_A,COLUMN_B) cnt1, 
           COUNT(*) OVER(PARTITION BY COLUMN_A,COLUMN_C) cnt2
    from SOME_TABLE t1
) t1
WHERE cnt1 = 1 AND cnt2 > 1

sqlfiddle

CodePudding user response：

You can use:

SELECT column_a, column_b, column_c
FROM   (
  SELECT t.*,
         COUNT(column_b) OVER (PARTITION BY column_a, column_c) AS cnt_all,
         COUNT(DISTINCT column_b) OVER (PARTITION BY column_a, column_c)
           AS cnt_dst
  FROM   table_name t
)
WHERE cnt_all = cnt_dst
AND   cnt_dst > 1

Which, for the sample data:

CREATE TABLE table_name (COLUMN_A, COLUMN_B, COLUMN_C) AS
SELECT '000111222', 'AAAAAAAAA', 100 FROM DUAL UNION ALL
SELECT '000111222', 'BBBBBBBBB', 100 FROM DUAL UNION ALL
SELECT '000111222', 'CCCCCCCCC', 300 FROM DUAL UNION ALL
SELECT '000111222', 'CCCCCCCCC', 300 FROM DUAL UNION ALL
SELECT '000888999', 'DDDDDDDDD', 300 FROM DUAL UNION ALL
SELECT '000888999', 'DDDDDDDDD', 300 FROM DUAL UNION ALL
SELECT '777666555', 'EEEEEEEEE', 100 FROM DUAL UNION ALL
SELECT '777666555', 'FFFFFFFFF', 100 FROM DUAL UNION ALL
SELECT '234567890', 'GGGGGGGGG', 100 FROM DUAL UNION ALL
SELECT '234567890', 'GGGGGGGGG', 100 FROM DUAL UNION ALL
SELECT '444333111', 'HHHHHHHHH', 100 FROM DUAL;

Outputs:

COLUMN_A	COLUMN_B	COLUMN_C
000111222	AAAAAAAAA	100
000111222	BBBBBBBBB	100
777666555	EEEEEEEEE	100
777666555	FFFFFFFFF	100

db<>fiddle here