WITH
    se AS (
    SELECT
        se.student_id,
        se.school_id,
        se.grade_id

    FROM
        student_enrollment se

    WHERE
        se.syear=2015
        AND (se.end_date IS NULL OR se.end_date >= current_date)
        AND se.custom_9 IS NOT NULL
    ),
    totalbridge AS (
        SELECT
            current_date::date,
            sg.short_name

        FROM
            school_gradelevels sg
    ),
    total AS (
        SELECT
            current_date::date,
            count(*) AS TOTAL

        FROM
            students s
            JOIN se ON (s.student_id=se.student_id)
    )

SELECT
    current_date::date,
    sc.custom_327 AS SCHOOL_ID,
    sg.short_name AS GRADE_LEVEL,
    COALESCE(SUM(CASE
        WHEN s.custom_200000001 = 'Asian or Pacific Islander' THEN 1
        ELSE 0
    END),0) AS ASIAN,
    COALESCE(SUM(CASE
        WHEN s.custom_200000001 = 'Black, Non-Hispanic' THEN 1
        ELSE 0
    END),0) AS BLACK,
    COALESCE(SUM(CASE
        WHEN s.custom_200000001 = 'Hispanic' THEN 1
        ELSE 0
    END),0) AS HISPANIC,
    COALESCE(SUM(CASE
        WHEN s.custom_200000001 = 'Amer. Indian or Alaskan Native' THEN 1
        ELSE 0
    END),0) AS INDN,
    COALESCE(SUM(CASE
        WHEN s.custom_200000001 = 'Other' THEN 1
        ELSE 0
    END),0) AS MULTIR,
    COALESCE(SUM(CASE
        WHEN s.custom_200000001 = 'White, Non-Hispanic' THEN 1
        ELSE 0
    END),0) AS WHITE,
    COALESCE(SUM(CASE
        WHEN s.custom_200000000 = 'F - Female' THEN 1
        ELSE 0
    END),0) AS FEMALE,
    COALESCE(SUM(CASE
        WHEN s.custom_200000000 = 'M - Male' THEN 1
        ELSE 0
    END),0) AS MALE,
    count(*) AS TOTAL,
    count(*) / total.total AS "%"

FROM
    students s
    JOIN se ON (s.student_id=se.student_id)
    JOIN schools sc ON (se.school_id=sc.id)
    JOIN school_gradelevels sg ON (se.grade_id=sg.id)
    JOIN totalbridge ON (sg.short_name=totalbridge.short_name)
    JOIN total ON (totalbridge.date=total.date)

GROUP BY
    1,2,3,total.total

UNION

SELECT
    current_date::date,
    'TOTAL',
    'TOTAL',
    COALESCE(SUM(CASE
        WHEN s.custom_200000001 = 'Asian or Pacific Islander' THEN 1
        ELSE 0
    END),0) AS ASIAN,
    COALESCE(SUM(CASE
        WHEN s.custom_200000001 = 'Black, Non-Hispanic' THEN 1
        ELSE 0
    END),0) AS BLACK,
    COALESCE(SUM(CASE
        WHEN s.custom_100000105 = 'Yes' THEN 1
        ELSE 0
    END),0) AS HISPANIC,
    COALESCE(SUM(CASE
        WHEN s.custom_200000001 = 'Amer. Indian or Alaskan Native' THEN 1
        ELSE 0
    END),0) AS INDN,
    COALESCE(SUM(CASE
        WHEN s.custom_200000001 = 'Other' THEN 1
        ELSE 0
    END),0) AS MULTIR,
    COALESCE(SUM(CASE
        WHEN s.custom_200000001 = 'White, Non-Hispanic' THEN 1
        ELSE 0
    END),0) AS WHITE,
    COALESCE(SUM(CASE
        WHEN s.custom_200000000 = 'F - Female' THEN 1
        ELSE 0
    END),0) AS FEMALE,
    COALESCE(SUM(CASE
        WHEN s.custom_200000000 = 'M - Male' THEN 1
        ELSE 0
    END),0) AS MALE,
    count(*) AS TOTAL,
    count(*) / total.total AS "%"

FROM
    students s
    JOIN se ON (s.student_id=se.student_id)
    JOIN school_gradelevels sg ON (se.grade_id=sg.id)
    JOIN totalbridge ON (sg.short_name=totalbridge.short_name)
    JOIN total ON (totalbridge.date=total.date)

GROUP BY
    1,2,3,total.total

ORDER BY
    "%",1,2,3;