Oracle display number of unique values for column combination

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