gistfile1.sql

SELECT
    CASE WHEN GROUPING(service) = 1 THEN 'All' ELSE service END service,
    SUM(CASE WHEN mm.service_grade_code = 'E01' THEN 1 ELSE 0 END) e01,
    100 * ROUND(CASE WHEN SUM(1) = 0 THEN 0 ELSE SUM(CASE WHEN mm.service_grade_code = 'E01' THEN 1 ELSE 0 END) / CAST(SUM(1) AS FLOAT) END, 4) e01_percentage,
    -- ... lots more service grade codes here
    SUM(CASE WHEN mm.service_grade_code = 'W05' THEN 1 ELSE 0 END) w05,
    100 * ROUND(CASE WHEN SUM(1) = 0 THEN 0 ELSE SUM(CASE WHEN mm.service_grade_code = 'W05' THEN 1 ELSE 0 END) / CAST(SUM(1) AS FLOAT) END, 4) w05_percentage,
    SUM(CASE WHEN mm.service_grade_code NOT IN ('E01','E02','E03','E04','E05','E06','E07','E08','E09','O01','O02','O03','O04','O05','O06','O07','O08','O09','W01','W02','W03','W04','W05') OR mm.service_grade_code IS NULL THEN 1 ELSE 0 END) unknown,
    100 * ROUND(
        CASE WHEN SUM(1) = 0 THEN
            0
        ELSE
            SUM(CASE WHEN mm.service_grade_code NOT IN ('E01','E02','E03','E04','E05','E06','E07','E08','E09','O01','O02','O03','O04','O05','O06','O07','O08','O09','W01','W02','W03','W04','W05') OR mm.service_grade_code IS NULL THEN 1 ELSE 0 END) / CAST(SUM(1) AS FLOAT)
        END, 4
    ) unknown_percentage,
    SUM(1) total
FROM
    evacuations e LEFT JOIN
    merged_movements mm ON e.id = mm.evacuation_id LEFT JOIN
    states s ON (s.state_machinable_id = e.id AND
    s.state_machinable_type = 'Evacuation') LEFT JOIN
    trauma_codes tc ON tc.id = e.trauma_code_id
WHERE
    mm.origin_theater = 'CENTCOM' AND
    (mm.destination_theater = 'CONUS' OR mm.destination_theater = 'EUCOM' OR mm.destination_theater = 'PACOM') AND
    mm.operation in ('OEF','OIF') AND
    s.type = 'CompletedState' AND
    mm.active_duty = 1 AND
    is_injury = 0
GROUP BY
    mm.service
WITH ROLLUP
ORDER BY
    mm.service