Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE temp_data
(transact_dt_tm DATE
, letter CHAR(1));
CREATE TABLE temp_ref
(
letter CHAR(1)
, letter_seq INT);
BEGIN
INSERT INTO temp_data (transact_dt_tm,letter) VALUES (DATE '2023-01-01','K');
INSERT INTO temp_data (transact_dt_tm,letter) VALUES (DATE '2023-01-02','K');
INSERT INTO temp_data (transact_dt_tm,letter) VALUES (DATE '2023-01-02','L');
INSERT INTO temp_data (transact_dt_tm,letter) VALUES (DATE '2023-01-03','Q');
INSERT INTO temp_data (transact_dt_tm,letter) VALUES (DATE '2024-01-02','Y');
INSERT INTO temp_data (transact_dt_tm,letter) VALUES (DATE '2024-01-03','B');
INSERT INTO temp_ref (letter, letter_seq) values('Y',10);
INSERT INTO temp_ref (letter, letter_seq) values('B',20);
INSERT INTO temp_ref (letter, letter_seq) values('Q',30);
INSERT INTO temp_ref (letter, letter_seq) values('K',40);
INSERT INTO temp_ref (letter, letter_seq) values('L',50);
END;
/
1 rows affected

SELECT td.year,
       tr.letter, 
       count(td.letter)
FROM   TEMP_REF tr
       LEFT OUTER JOIN (
         SELECT EXTRACT(YEAR FROM transact_dt_tm) AS year,
                letter
         FROM   TEMP_DATA
       ) td
       PARTITION BY (td.year)
       ON tr.LETTER = td.LETTER 
GROUP BY 
       td.year,
       tr.LETTER_SEQ,
       tr.LETTER 
ORDER BY 
       td.year,
       tr.LETTER_SEQ;


YEAR
LETTER
COUNT(TD.LETTER)


2023
Y
0

2023
B
0

2023
Q
1

2023
K
2

2023
L
1

2024
Y
1

2024
B
1

2024
Q
0

2024
K
0

2024
L
0

YEAR	LETTER	COUNT(TD.LETTER)
2023	Y	0
2023	B	0
2023	Q	1
2023	K	2
2023	L	1
2024	Y	1
2024	B	1
2024	Q	0
2024	K	0
2024	L	0