Selecting the demographic data from Foothill Enrollment

foothill.sql

-- All that mess of parens is b/c, if you're left joining, you have to do
-- ((a LEFT JOIN b) LEFT JOIN c) and so on; a LEFT JOIN b LEFT JOIN c does not work
SELECT a.Zip_Code, a.total, af.africanamerican, asi.asian, f.filipino, h.hispanic, me.multiethnic, fn.firstnations, o.other, p.pacificislander,
       u.unrecorded, w.white
INTO enrollment_by_zip
FROM
((((((((((SELECT Zip_Code, count(*) AS total FROM enrollment GROUP BY Zip_Code) a LEFT JOIN
(SELECT Zip_Code, count(*) AS africanamerican FROM enrollment WHERE ethnicity='African American' GROUP BY Zip_Code) af ON a.zip_code = af.zip_code) LEFT JOIN
(SELECT Zip_Code, count(*) AS asian FROM enrollment WHERE ethnicity='Asian' GROUP BY Zip_Code) asi ON a.zip_code = asi.zip_code) LEFT JOIN
(SELECT Zip_Code, count(*) AS filipino FROM enrollment WHERE ethnicity='Filipino' GROUP BY Zip_Code) f ON a.zip_code = f.zip_code) LEFT JOIN
(SELECT Zip_Code, count(*) AS hispanic FROM enrollment WHERE ethnicity='Hispanic' GROUP BY Zip_Code) h ON a.zip_code = h.zip_code) LEFT JOIN
(SELECT Zip_Code, count(*) AS multiethnic FROM enrollment WHERE ethnicity='Multi-Ethnic' GROUP BY Zip_Code) me ON a.zip_code = me.zip_code) LEFT JOIN
(SELECT Zip_Code, count(*) AS firstnations FROM enrollment WHERE ethnicity='Native American' GROUP BY Zip_Code) fn ON a.zip_code = fn.zip_code) LEFT JOIN
(SELECT Zip_Code, count(*) AS other FROM enrollment WHERE ethnicity='Other' GROUP BY Zip_Code) o ON a.zip_code = o.zip_code) LEFT JOIN
(SELECT Zip_Code, count(*) AS pacificislander FROM enrollment WHERE ethnicity='Pacific Islander' GROUP BY Zip_Code) p ON a.zip_code = p.zip_code) LEFT JOIN
(SELECT Zip_Code, count(*) AS unrecorded FROM enrollment WHERE ethnicity='Unrecorded' GROUP BY Zip_Code) u ON a.zip_code = u.zip_code) LEFT JOIN
(SELECT Zip_Code, count(*) AS white FROM enrollment WHERE ethnicity='White' GROUP BY Zip_Code) w ON a.zip_code = w.zip_code;

-- Set nulls to 0. Access does not give us an NVL or NZ function in ODBC.
UPDATE enrollment_by_zip SET africanamerican = 0 WHERE africanamerican IS NULL;
UPDATE enrollment_by_zip SET asian = 0 WHERE asian IS NULL;
UPDATE enrollment_by_zip SET filipino = 0 WHERE filipino IS NULL;
UPDATE enrollment_by_zip SET hispanic = 0 WHERE hispanic IS NULL;
UPDATE enrollment_by_zip SET multiethnic = 0 WHERE multiethnic IS NULL;
UPDATE enrollment_by_zip SET firstnations = 0 WHERE firstnations IS NULL;
UPDATE enrollment_by_zip SET other = 0 WHERE other IS NULL;
UPDATE enrollment_by_zip SET pacificislander = 0 WHERE pacificislander IS NULL;
UPDATE enrollment_by_zip SET unrecorded = 0 WHERE unrecorded IS NULL;
UPDATE enrollment_by_zip SET white = 0 WHERE white IS NULL;
DELETE FROM enrollment_by_zip WHERE zip_code IS NULL; -- since you can't join nulls, these rows will be null-filled and useless

-- Lastly, run this; if it returns any rows you did the join wrong (or more likely one of the
-- updates, i.e. you updated one column based on another not based on itself)
SELECT zip_code, 
   (total - (africanamerican + asian + filipino + hispanic + multiethnic + firstnations + other + pacificislander + unrecorded + white)) as diff
FROM enrollment_by_zip
WHERE (total - (africanamerican + asian + filipino + hispanic + multiethnic + firstnations + other + pacificislander + unrecorded + white)) <> 0;