I have a table which contains socio-economic information about different community areas of chicago, such as percentage of homeless people, percentage of households below poverty, etc. Now what I want to do is create bins for the values of each field, such that if the percentage is below the average of all the rows then the value is set to 0, while if the percentage is equal or above average it is set to 1.
I want to do this so that I can then find association rules using the bins.
Till now I've come up with the below query:
WITH avg AS (SELECT AVG(crowded_housing) AS ch from
algorithms.socioeconomic_bins)
UPDATE algorithms.socioeconomic_bins SET crowded_housing=0 where
crowded_housing<(SELECT ch FROM avg);
This works per se but since I have 8 columns I want to bin, I would have to run this query twice for each field (for below and above avg). I couldn't find any helpful pages or questions. Any ideas please?