SELECT
    LECTURERCODE,
    NIP,
    EMPLOYEESNAME,
    TIPEDOSEN,
    ACADEMICGRADESID,
    SALARY,
    ROUND(NVL(SKS,
    0),
    2) SKS,
    ROUND(NVL(SKS_KOOR,
    0),
    2) SKS_KOOR,
    ROUND(NVL(SKS,
    0)+ NVL(SKS_KOOR,
    0),
    2) TOTALSKS
FROM
    LECTURERVIEW
JOIN (
    SELECT
        LECTURERCODE,
        SUM(SKS) SKS
    FROM
        (
        SELECT
            LECTURERCODE,
            SUM(CREDIT_MAIN_LECTURECODE) SKS
        FROM
            COURSE
        JOIN SUBJECTS
                USING (SUBJECTID)
        WHERE
            ACTIVATEDSTATUS = 'YA'
            AND LECTURERCODE IS NOT NULL
            AND SCHOOLYEAR = '2324'
            AND SEMESTER = '2'
        GROUP BY
            LECTURERCODE
    UNION ALL
        SELECT
            A.LECTURERCODE,
            SUM(A.CREDIT) SKS
        FROM
            COURSE_TEACHING_TEAM A
        JOIN COURSE
                USING (COURSEID)
        JOIN SUBJECTS
                USING (SUBJECTID)
        WHERE
            ACTIVATEDSTATUS = 'YA'
            AND A.LECTURERCODE IS NOT NULL
            AND SCHOOLYEAR = '2324'
            AND SEMESTER = '2'
        GROUP BY
            A.LECTURERCODE
                    )
    GROUP BY
        LECTURERCODE)
        USING (LECTURERCODE)
LEFT JOIN (
    SELECT
        COURSECOORDINATOR,
        COUNT(DISTINCT SUBJECTID) SKS_KOOR
    FROM
        COURSE
    JOIN SUBJECTS
            USING (SUBJECTID)
    WHERE
        ACTIVATEDSTATUS = 'YA'
        AND COURSECOORDINATOR IS NOT NULL
        AND SCHOOLYEAR = '2324'
        AND SEMESTER = '2'
    GROUP BY
        COURSECOORDINATOR) ON
    (COURSECOORDINATOR = LECTURERCODE)
ORDER BY
    NIP ASC