Get Counts, then Group By Values

Question

I have a table in my Oracle SQL database as follows:

DATE_A	DATE_B
07-2025	07-2025
05-2025	05-2025
03-2025	03-2025
01-2025	02-2025
01-2025	01-2025
01-2025	01-2025
01-2025	03-2025
04-2025	03-2025

I'd like to count the number of times that a date shows up in a group, to get a result as follows:

DATE	COUNT_A	COUNT_B
07-2025	1	1
05-2025	1	1
04-2025	1	0
03-2025	1	3
02-2025	0	1
01-2025	4	2

How might I accomplish this as efficiently as possible?

Answer 1

From Oracle 12, you can use a lateral join:

SELECT l.dt,
       SUM(a) AS count_a,
       SUM(b) AS count_b
FROM   table_name
       CROSS JOIN LATERAL(
         SELECT date_a AS dt, 1 AS a, 0 AS b FROM DUAL UNION ALL
         SELECT date_b AS dt, 0 AS a, 1 AS b FROM DUAL
       ) l
GROUP BY l.dt

Which, for the sample data:

CREATE TABLE table_name (DATE_A, DATE_B) AS
SELECT '07-2025', '07-2025' FROM DUAL UNION ALL
SELECT '05-2025', '05-2025' FROM DUAL UNION ALL
SELECT '03-2025', '03-2025' FROM DUAL UNION ALL
SELECT '01-2025', '02-2025' FROM DUAL UNION ALL
SELECT '01-2025', '01-2025' FROM DUAL UNION ALL
SELECT '01-2025', '01-2025' FROM DUAL UNION ALL
SELECT '01-2025', '03-2025' FROM DUAL UNION ALL
SELECT '04-2025', '03-2025' FROM DUAL;

Outputs:

DT	COUNT_A	COUNT_B
01-2025	4	2
03-2025	1	3
04-2025	1	0
02-2025	0	1
05-2025	1	1
07-2025	1	1

fiddle

Answer 2

That's the solution I'll prefer, but with subqueries. First we'll get distinct dates from both fields, then calculate counts in subqueries.

WITH dates AS (
  SELECT DATE_A d FROM table
  UNION 
  SELECT DATE_B FROM table
)
SELECT 
  d1.d "DATE",
  (SELECT COUNT(*) FROM table d2 WHERE d2.DATE_A=d1.d) COUNT_A,
  (SELECT COUNT(*) FROM table d2 WHERE d2.DATE_B=d1.d) COUNT_B,
FROM dates d1

Another way to group with cases. First we will get all the A-dates and add type 'A', then same for B, then group.

WITH dates AS (
  SELECT DATE_A d, 'A' type FROM table
  UNION ALL
  SELECT DATE_B, 'B' FROM table
)
SELECT 
  d "DATE", 
  COUNT(CASE WHEN type='A' THEN 1 END) COUNT_A,
  COUNT(CASE WHEN type='B' THEN 1 END) COUNT_B
FROM dates
GROUP BY d

Take in account, that WITH also subquery :•)
Unfortunately I don't see solutions without them.

https://dbfiddle.uk/Q5kKzv_p