SQL notes

#Select a column for all items
    SELECT column FROM ma_data;

#Select a column with some limit (replace column, 2000, and the > operator with whatever you want)
    SELECT column FROM ma_data WHERE column > 2000;

#Get amount of records by zip code
    SELECT zip_code, count(zip_code) AS "number of records" FROM ma_data GROUP BY zip_code

#Select an average of a column grouped by a zip code (note - if the column isn't a number, it will probably fail)
    SELECT zip_code, avg(column) FROM ma_data GROUP BY zip_code;

#Select an average of a column grouped by a zip code with a HAVING clause (i.e., a WHERE clause. WHERE and HAVING do basically the same thing, but for aggregate functions you have to use HAVING instead of WHERE)
    SELECT zip_code, avg(column) FROM ma_data GROUP BY zip_code HAVING avg(column) > 80000;


#Generate some value based on selections 
    SELECT zip_code, avg(E00200) + avg(E00100) AS "combined data" FROM ma_data;


#Note that you can break things up with new lines, it's not necessary but makes things a little easier to read
#PostgreSQL supports mathematical operations, so you can do some more fancy stuff too, e.g.,: 
    SELECT zip_code, ( ((avg(E00200) / avg(E00100)) + sum(E00200)) / 42 ) AS "combined data" 
    FROM ma_data 
    GROUP BY zip_code;

#Conditionals
SELECT
zip_code,
(CASE WHEN (avg(E00200) < 50000)
    THEN avg(E00200)
    ELSE 400
END) as value1,
(CASE WHEN (avg(E00200) > 49999)
    THEN avg(E00200)
    ELSE 400
END) as value2
FROM ma_data
GROUP BY zip_code;

#Dummy deduction
    SELECT ( ((E00200 - 4400) * .05) - ( (E00200 - 8800) * .1) )
    FROM ma_data
    WHERE mars='MS'