SELECT
    STUDENTSCHOOLYEAR,
    ACTIVE,
    GRADUATED,
    PASSEDAWAY,
    DROPOUT,
    RESIGN,
    UNKNOWN,
    LEAVE,
    NONACTIVE,
    COUNT(*) AS TOTAL
FROM
    MASTERDATA.STUDENT
JOIN MASTERDATA.STUDYPROGRAM
        USING (STUDYPROGRAMID)
LEFT JOIN
    (
    SELECT
        STUDENTSCHOOLYEAR,
        COUNT(*) ACTIVE
    FROM
        MASTERDATA.STUDENT
    JOIN MASTERDATA.STUDYPROGRAM
            USING (STUDYPROGRAMID)
    WHERE
        STUDENTTYPEID =
                    '1'
        AND STUDYPROGRAMID = '13'
        AND TO_CHAR(STARTDATE, 'YYYY')
<= '2122'
        AND (TO_CHAR(OUTDATE, 'YYYY' )>
    '2122'
            OR OUTDATE IS NULL)
    GROUP BY
        (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
LEFT JOIN
                    (
    SELECT
        STUDENTSCHOOLYEAR,
        COUNT(*) PASSEDAWAY
    FROM
        MASTERDATA.STUDENT
    JOIN MASTERDATA.STUDYPROGRAM
            USING (STUDYPROGRAMID)
    WHERE
        STUDENTTYPEID =
                    '5'
        AND STUDYPROGRAMID = '13'
        AND TO_CHAR(STARTDATE, 'YYYY')
    <= '2122'
            AND (TO_CHAR(OUTDATE, 'YYYY' )= '2122'
                OR OUTDATE IS NULL)
        GROUP BY
            (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
LEFT JOIN (
    SELECT
        STUDENTSCHOOLYEAR,
        COUNT(*) GRADUATED
    FROM
        MASTERDATA.STUDENT
    JOIN MASTERDATA.STUDYPROGRAM
            USING (STUDYPROGRAMID)
    WHERE
        STUDENTTYPEID = '2'
        AND STUDYPROGRAMID = '13'
        AND TO_CHAR(STARTDATE, 'YYYY' )<= '2122'
            AND (TO_CHAR(OUTDATE, 'YYYY') = '2122'
                OR OUTDATE IS NULL)
        GROUP BY
            (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
LEFT JOIN
                    (
    SELECT
        STUDENTSCHOOLYEAR,
        COUNT(*) DROPOUT
    FROM
        MASTERDATA.STUDENT
    JOIN MASTERDATA.STUDYPROGRAM
            USING (STUDYPROGRAMID)
    WHERE
        STUDENTTYPEID =
                    '6'
        AND STUDYPROGRAMID = '13'
        AND TO_CHAR(STARTDATE, 'YYYY') <= '2122'
            AND (TO_CHAR(OUTDATE, 'YYYY' )= '2122'
                OR OUTDATE IS NULL)
        GROUP BY
            (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
LEFT JOIN (
    SELECT
        STUDENTSCHOOLYEAR,
        COUNT(*) UNKNOWN
    FROM
        MASTERDATA.STUDENT
    JOIN MASTERDATA.STUDYPROGRAM
            USING (STUDYPROGRAMID)
    WHERE
        (STUDENTTYPEID IN ('7' , '9' , '5' )
            OR STUDENTTYPEID IS NULL)
        AND STUDYPROGRAMID = '13'
        AND TO_CHAR(STARTDATE, 'YYYY' )<= '2122'
            AND (TO_CHAR(OUTDATE, 'YYYY') > '2122'
                OR OUTDATE IS NULL)
        GROUP BY
            (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
LEFT JOIN
                    (
    SELECT
        STUDENTSCHOOLYEAR,
        COUNT(*) RESIGN
    FROM
        MASTERDATA.STUDENT
    JOIN MASTERDATA.STUDYPROGRAM
            USING (STUDYPROGRAMID)
    WHERE
        STUDENTTYPEID =
                    '3'
        AND STUDYPROGRAMID = '13'
        AND TO_CHAR(STARTDATE, 'YYYY') <= '2122'
            AND (TO_CHAR(OUTDATE, 'YYYY' )= '2122'
                OR OUTDATE IS NULL)
        GROUP BY
            (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
LEFT JOIN (
    SELECT
        STUDENTSCHOOLYEAR,
        COUNT(*) LEAVE
    FROM
        MASTERDATA.STUDENT
    JOIN MASTERDATA.STUDYPROGRAM
            USING (STUDYPROGRAMID)
    WHERE
        STUDENTTYPEID = '8'
        AND STUDYPROGRAMID = '13'
        AND TO_CHAR(STARTDATE, 'YYYY' )<= '2122'
            AND (TO_CHAR(OUTDATE, 'YYYY') > '2122'
                OR OUTDATE IS NULL)
        GROUP BY
            (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
LEFT JOIN
                    (
    SELECT
        STUDENTSCHOOLYEAR,
        COUNT(*) NONACTIVE
    FROM
        MASTERDATA.STUDENT
    JOIN MASTERDATA.STUDYPROGRAM
            USING (STUDYPROGRAMID)
    WHERE
        STUDENTTYPEID IN ('10')
            AND STUDYPROGRAMID = '13'
            AND TO_CHAR(STARTDATE, 'YYYY') <= '2122'
                AND (TO_CHAR(OUTDATE, 'YYYY' )>
        '2122'
                    OR OUTDATE IS NULL)
            GROUP BY
                (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
WHERE
    1 = 1
    AND STUDYPROGRAMID = '13'
    AND TO_CHAR(STARTDATE, 'YYYY') <= '2122'
    AND (TO_CHAR(OUTDATE, 'YYYY' )>'2122'
        OR OUTDATE IS NULL)
GROUP BY
    STUDENTSCHOOLYEAR,
    ACTIVE,
    GRADUATED,
    PASSEDAWAY,
    DROPOUT,
    RESIGN,
    UNKNOWN,
    LEAVE,
    NONACTIVE
ORDER BY
    STUDENTSCHOOLYEAR