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'
    GROUP BY
        (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
LEFT JOIN
                    (
    SELECT
        STUDENTSCHOOLYEAR,
        COUNT(*) PASSEDAWAY
    FROM
        MASTERDATA.STUDENT
    JOIN MASTERDATA.STUDYPROGRAM
            USING (STUDYPROGRAMID)
    WHERE
        STUDENTTYPEID =
                    '5'
    GROUP BY
        (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
LEFT JOIN
                    (
    SELECT
        STUDENTSCHOOLYEAR,
        COUNT(*) GRADUATED
    FROM
        MASTERDATA.STUDENT
    JOIN MASTERDATA.STUDYPROGRAM
            USING (STUDYPROGRAMID)
    WHERE
        STUDENTTYPEID =
                    '2'
    GROUP BY
        (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
LEFT JOIN
                    (
    SELECT
        STUDENTSCHOOLYEAR,
        COUNT(*) DROPOUT
    FROM
        MASTERDATA.STUDENT
    JOIN MASTERDATA.STUDYPROGRAM
            USING (STUDYPROGRAMID)
    WHERE
        STUDENTTYPEID =
                    '6'
    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)
    GROUP BY
        (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
LEFT JOIN
                    (
    SELECT
        STUDENTSCHOOLYEAR,
        COUNT(*) RESIGN
    FROM
        MASTERDATA.STUDENT
    JOIN MASTERDATA.STUDYPROGRAM
            USING (STUDYPROGRAMID)
    WHERE
        STUDENTTYPEID =
                    '3'
    GROUP BY
        (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
LEFT JOIN
                    (
    SELECT
        STUDENTSCHOOLYEAR,
        COUNT(*) LEAVE
    FROM
        MASTERDATA.STUDENT
    JOIN MASTERDATA.STUDYPROGRAM
            USING (STUDYPROGRAMID)
    WHERE
        STUDENTTYPEID =
                    '8'
    GROUP BY
        (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
LEFT JOIN
                    (
    SELECT
        STUDENTSCHOOLYEAR,
        COUNT(*) NONACTIVE
    FROM
        MASTERDATA.STUDENT
    JOIN MASTERDATA.STUDYPROGRAM
            USING (STUDYPROGRAMID)
    WHERE
        STUDENTTYPEID IN ('10')
    GROUP BY
        (STUDENTSCHOOLYEAR))
        USING (STUDENTSCHOOLYEAR)
WHERE
    1 = 1
GROUP BY
                    (STUDENTSCHOOLYEAR,
    ACTIVE,
    GRADUATED,
    PASSEDAWAY,
    DROPOUT,
    RESIGN,
    UNKNOWN,
    LEAVE,
    NONACTIVE)
ORDER BY
    STUDENTSCHOOLYEAR