How to bin arbitrarily in MySQL?

2
votes

I have a table with a column that lists ages of users. I want to bin ages in arbitrary groupings (13-17,18-25, etc) and then be able to group by those bins and count users in each group. How can I accomplish this in a query?

2
mysqlsql
I got confused when you used the term 'bin'. The word I hear used most often is 'group', as in 'grouping records together'. So your statement "I want to bin ages in arbitrary groupings" could be worded clearer as "I want to group based on age". - Jesse Webb
I think it's a subtle difference, but I would prefer to use "group" as in "group by" for the last operation in a SQL statement. "Binning" is just changing the label of a value, but doesn't necessarily have to be followed by a group by operation. Different strokes... - Evan Zamir

2 Answers

5
votes
SELECT CASE WHEN age BETWEEN 13 AND 17 THEN '13-17' 
            WHEN age BETWEEN 18 AND 25 THEN '18-25' 
            ELSE '26+' END AS AgeGroup,
    COUNT(*) AS total
FROM MyTable
GROUP BY AgeGroup
0
votes
SELECT
  COUNT(CASE WHEN `age` BETWEEN 13 AND 17 THEN 1 END) `13-17`,
  COUNT(CASE WHEN `age` BETWEEN 18 AND 25 THEN 2 END) `18-25`,
  COUNT(CASE WHEN `age` > 25 THEN 3 END) `> 25`
FROM tableListOfAges;