Group by two columns with OR logic ignoring nulls (Oracle)

Question

II have the table that holds two ID values. The key is combination of that ID, one of ID may be null but not both. The keys are compared ignoring null values. Something like that:

ID1  ID2   TIMESTAMP
___________________________
A    1     2023-09-01 11:00
A    null  2023-09-01 10:00
B    null  2023-09-01 09:00
null 1     2023-09-01 08:00 
B    2     2023-09-01 07:00

According to data model ID1 shoult match to at most one ID2 (so case A 1, B 1 is not possible). I need to count rows by the keys, i.e. rows 1,2,4 and rows 3,5 should be counted as the same and group by should produce the result something like that:

ID1 ID2 COUNT
_____________
A    1    3
B    2    2

Is it possible to do that with SQL? I am on Oracle 19c.

Answer 1

You can use a correlated sub-query:

SELECT id1,
       id2,
       ( SELECT COUNT(*)
         FROM   table_name c
         WHERE  ( c.id1 = t.id1 AND c.id2 = t.id2 )
         OR     ( c.id1 = t.id1 AND c.id2 IS NULL )
         OR     ( c.id1 IS NULL AND c.id2 = t.id2 )
       ) AS cnt
FROM   table_name t
WHERE  id1 IS NOT NULL
AND    id2 IS NOT NULL;

Which, for the sample data:

CREATE TABLE table_name (ID1, ID2, TIMESTAMP) AS
SELECT 'A',  1,     DATE '2023-09-01' + INTERVAL '11:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 'A',  null,  DATE '2023-09-01' + INTERVAL '10:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 'B',  null,  DATE '2023-09-01' + INTERVAL '09:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT null, 1,     DATE '2023-09-01' + INTERVAL '08:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 'B',  2,     DATE '2023-09-01' + INTERVAL '07:00' HOUR TO MINUTE FROM DUAL;

Outputs:

ID1	ID2	CNT
A	1	3
B	2	2

fiddle