How to get 2 distinct rows from 1 row with 3 tables in sql oracle?

I have 3 tables that looks a bit like this.

PersonTable

TypeToPersonTable

RequestSatusTable

I need to calculate how many of which status id there are for each person. So the result has to look like this.

I’ve tried using distinct like this but it’s not working.

SELECT PersonTable.Perosn AS person, 
    (DISTINCT RequestSatusTable.StatusId) AS status,
    COUNT(RequestSatusTable.StatusId)
FROM   PersonTable
JOIN TypeToPersonTable ON PersonTable.ID = TypeToPersonTable.PersonID
JOIN RequestSatusTable ON RequestSatusTable.SPECID= TypeToPersonTable.SPECID
GROUP BY PersonTable.Person

I can’t figure it out. Any help would be much appreciated.

>Solution :

Include statusid in the GROUP BY and use a CASE expression to conver the 1/0 of statusid to good/bad:

SELECT p.Person,
       CASE r.StatusId WHEN 1 THEN 'good' ELSE 'bad' END AS status,
       COUNT(*) AS count
FROM   PersonTable p
       JOIN TypeToPersonTable t
       ON p.ID = t.PersonID
       JOIN RequestSatusTable r
       ON r.SPECID= t.SPECID
GROUP BY
       p.Person,
       r.StatusId

Which, for the sample data:

CREATE TABLE PersonTable (ID, Person) AS
SELECT 1, 'name1' FROM DUAL UNION ALL
SELECT 2, 'name2' FROM DUAL UNION ALL
SELECT 3, 'name3' FROM DUAL;

CREATE TABLE TypeToPersonTable (SPECID, PersonID) AS
SELECT 4, 1 FROM DUAL UNION ALL
SELECT 8, 1 FROM DUAL UNION ALL
SELECT 10, 3 FROM DUAL;

CREATE TABLE RequestSatusTable (SPECID, StatusID) AS
SELECT 4, 1 FROM DUAL UNION ALL
SELECT 4, 1 FROM DUAL UNION ALL
SELECT 4, 0 FROM DUAL UNION ALL
SELECT 8, 0 FROM DUAL UNION ALL
SELECT 8, 1 FROM DUAL;

Outputs:

fiddle