Oracle SQL - Remove Null rows in aliased results

Question

Hi devs I'm developing a small pro bono project that's use oracle sql but I'm not able to hide the null results.

Table Structure:

CREATE TABLE "church-members" (
ID NUMBER(10),
NAME varchar(30)  NOT NULL,
LOGIN varchar(20)  NOT NULL,
PASS  varchar(12)  NOT NULL,
REGISTER_YEAR_MONTH varchar(15)  NOT NULL,
USER_SCORE NUMBER(10),
PRIMARY KEY (ID));

The queries:

INSERT INTO "church-members" VALUES
('1', 'John Doe', 'John', 'Xo8*d_d%f58*', '202204','1');

INSERT INTO "church-members" VALUES
('2', 'Mary Doe', 'Mary', 'dLoc&257dsew', '202203','2');

INSERT INTO "church-members" VALUES
('3', 'Robertson III', 'Robertson', 'koIIf59*Liu*', '202203','7');

INSERT INTO "church-members" VALUES
('4', 'Sonia MacDonald', 'Sonia', 'fYhfgtdjfi%', '202204','4');

INSERT INTO "church-members" VALUES
('5', 'Boris Johnston', 'Boris', 'do*&flddkIK%', '202201','2');

INSERT INTO "church-members" VALUES
('6', 'Ruth Henderson', 'Ruth', 'dF6%*&', '202202','2');

The Select:

SELECT 
    ID,
    NAME,
    LOGIN,
    MAX(CASE WHEN REGISTER_YEAR_MONTH = '202203' THEN TO_CHAR(USER_SCORE) ELSE '' END) AS "MARCH SCORE",
    MAX(CASE WHEN REGISTER_YEAR_MONTH = '202204' THEN TO_CHAR(USER_SCORE) ELSE '' END) AS "APRIL SCORE "
FROM 
    "church-members" 

GROUP BY 
    ID,
    NAME,
    LOGIN

And the result fiddle:

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=a4deac5e3eefb17dca97661552458a61

I got to this point using the information obtained in the answer from this link: Select more than one column and remove NULL values from result

Looking at the fiddle example, the results with IDs 5 and 6 should not be showing because both are nulls. But still null results are being shown...

Can anyone help me to solve it?

Answer 1

Add a HAVING clause requiring that each matching ID have at least data for at least one of the two months:

SELECT ID, NAME, LOGIN,
       MAX(CASE WHEN REGISTER_YEAR_MONTH = '202203' THEN TO_CHAR(USER_SCORE) END) AS "MARCH SCORE",
       MAX(CASE WHEN REGISTER_YEAR_MONTH = '202204' THEN TO_CHAR(USER_SCORE) END) AS "APRIL SCORE"
FROM "church-members"
GROUP BY ID, NAME, LOGIN
HAVING COUNT(CASE WHEN REGISTER_YEAR_MONTH IN ('202203', '202204') THEN 1 END) > 0;