How to write MYSQL query for frequency count but with variable bin size?

Question

I use the following query to calculate the age of people from their dob and then group the ages in ten year intervals to make a frequency chart.

I'd prefer the user to be able to choose the bin size instead of always having to use 10 years, eg maybe group the ages in 5 year intervals, 20 year intervals or any arbitrary range.

How should I re-write the query so that the bin size (currently 10 years) can be passed as a parameter or maybe picked up from another table that is pre-populated with the bins just before running the query?

Obviously I won't be able to use a hard coded CASE in the same way, if at all. Can it be done?

SELECT
   CASE
      WHEN age = -1                THEN 0 -- null dob
      WHEN age >= 0  AND age < 11  THEN 1
      WHEN age >= 11 AND age < 21  THEN 2
      WHEN age >= 21 AND age < 31  THEN 3
      WHEN age >= 31 AND age < 41  THEN 4
      WHEN age >= 41 AND age < 51  THEN 5
      WHEN age >= 51 AND age < 61  THEN 6
      WHEN age >= 61 AND age < 71  THEN 7
      WHEN age >= 71 AND age < 81  THEN 8
      WHEN age >= 81 AND age < 91  THEN 9
      WHEN age >= 91 AND age < 101 THEN 10
      WHEN age > 100               THEN 11 
   END AS Age_Group,
   COUNT(age) AS Number_In_Group
FROM
      -- this sub query calculates the age from the dob
      -- returning -1 if dob is null
   (SELECT     
    IFNULL(  
             DATE_FORMAT(NOW(), '%Y')  
           - DATE_FORMAT(dob, '%Y') 
           - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d'))
           , - 1
          ) AS age
   FROM
      people
   ) AS table_age

GROUP BY Age_Group

This produces the following typical output

Age_Group  Number_In_Group  
0          55               
2          1                
3          37               
4          47               
5          51               
6          112              
7          139              
8          70               
9          30               
10         6

user3209752 user3209752 · Accepted Answer · 2016-04-19T08:02:57

I think I may have solved this myself so am posting my answer in case it helps anyone else. (or if somebody has a better way!)

The trick seems to be to use a stored procedure (so that the bin increment can be passed in as a parameter).

The procedure first makes a temp table for the bins and then populates it in a while loop using the desired bin_size parameter to set the minimum and maximum ages for each bin.

Finally it does a normal grouped count, grouping on the bin label by joining the table with date of births in it (from which it calculates ages) to the temp table with the frequency bins in it. A left join is used to ensure that all bin labels get returned, even if the count is zero.

Seems to run surprisingly fast as well, eg 0.03s using 1000 dates of birth and a bin frequency of 15 years.

usage: eg to get a frequency table in steps of 15 years call this procedure using

CALL age_frequency_count(15);

This is the code

DELIMITER $$

CREATE  PROCEDURE age_frequency_count(IN bin_size INT)
BEGIN
    DECLARE bin_min_age INT;         -- minimum age for bin
    DECLARE bin_max_age INT;         -- maximum age for bin
    DECLARE bin_label VARCHAR(8);    -- label for bin

-- #########################################   
    -- make a temporary table for the bins if it doesn't exist
    CREATE TEMPORARY TABLE IF NOT EXISTS tbl_bins
        ( minage INT, maxage INT, agegroup VARCHAR(30) ) ;

-- #########################################         
    -- empty it, in case it did already exist    
    DELETE FROM tbl_bins; 

-- #########################################     
    -- loop round, populating temp table using bin_size up to around 100 yrs old 
    SET bin_min_age = 0;
    WHILE (bin_min_age + bin_size) < 100 DO 
         SET bin_max_age = bin_min_age + bin_size ; 
         SET bin_label = CONCAT(bin_min_age ,' to ' , bin_max_age); 
         INSERT INTO tbl_bins VALUES (bin_min_age, bin_max_age, bin_label); 
         SET bin_min_age = bin_max_age; 
    END WHILE;

    -- now insert bin for any age above around 100 yrs old (up to 200 yrs old)
    INSERT INTO tbl_bins VALUES (bin_min_age, 200, CONCAT('over ', bin_max_age)); 

    -- and a bin for any ages of -1 that were generated due to a null dob
    INSERT INTO tbl_bins VALUES (-1, -1, 'unknown'); 

-- #########################################  
-- finally select the age counts grouped into bins by joining the 'people'
-- table containing the dob to the bins table we just made and populated
SELECT
   agegroup,
   COUNT(age) AS NumberInGroup
FROM
   tbl_bins
   LEFT JOIN   -- LEFT so that we will still get zero counts if necessary
      (SELECT  -- next few lines calculate the age from the dob in the people table
         IFNULL(
                DATE_FORMAT(NOW(), '%Y') 
              - DATE_FORMAT(member_dob, '%Y') 
              - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(member_dob, '00-%m-%d') )
           ,  - 1    -- if dob is null set the age to -1
               ) AS age
      FROM people
      ) AS tbl_ages

   ON 
   (tbl_ages.age > tbl_bins.minage AND tbl_ages.age <= tbl_bins.maxage) -- normal bin
   OR (tbl_ages.age = tbl_bins.maxage) -- to account for age of -1

GROUP BY agegroup;

-- #########################################  
END$$

DELIMITER ;

How to write MYSQL query for frequency count but with variable bin size?

1 Answers