The donor-age distribution from the previous example has been constructed without further aggregation. To reduce the result length, the following query sums the number of unique donors per age band using the
CASE WHEN statement in the MySQL dialect.
For more advanced users, there are a number of statements and functions available for manipulating the data inside the SQL query in the MySQL 5.7 Manual.
SELECT CMTE_ID, CASE WHEN AGE < 21 THEN '<21' WHEN AGE >= 21 AND AGE <= 35 THEN '21-35' WHEN AGE >= 36 AND AGE <= 45 THEN '36-45' WHEN AGE >= 46 AND AGE <= 55 THEN '46-55' WHEN AGE >= 56 AND AGE <= 65 THEN '56-65' ELSE '65+' END AS AGE_BAND, COUNT(*) FROM ContributionsByIndividuals WHERE CYCLE = 2020 AND CMTE_ID IN ('C00580100', 'C00696948', 'C00703975') AND AGE IS NOT NULL GROUP BY CMTE_ID, AGE_BAND;
Updated over 2 years ago