Oracle display number of unique values for column combination

Tags: ,



I have the below table

C1 C2 C3
A1 A2 A3
B1 B2 B3
C1 C2 C3
C1 C4 C3

For each unique combination of C1 and C3, I would like to keep the first value in C2, but storing the count of unique elements in C2 (for the mentioned combination C1, C3). I would expect the below result:

O1 O2 O3 O4
A1 A2 A3 1
B1 B2 B3 1
C1 C2 C3 2

Answer

Results set in Oracle are unordered so asking for the first C2 row does not make sense unless you have some way of defining how to get the first. If you just want the minimum value then:

SELECT c1 AS o1,
       MIN( c2 ) AS o2,
       c3 As o3,
       COUNT(*) As o4
FROM   table_name
GROUP BY
       c1,
       c3;

Which, for the sample data:

CREATE TABLE table_name ( C1, C2, C3 ) AS
SELECT 'A1', 'A2', 'A3' FROM DUAL UNION ALL
SELECT 'B1', 'B2', 'B3' FROM DUAL UNION ALL
SELECT 'C1', 'C2', 'C3' FROM DUAL UNION ALL
SELECT 'C1', 'C4', 'C3' FROM DUAL;

Outputs:

O1 | O2 | O3 | O4
:- | :- | :- | -:
A1 | A2 | A3 |  1
C1 | C2 | C3 |  2
B1 | B2 | B3 |  1

db<>fiddle here



Source: stackoverflow