Search code examples
mysqldatabasedatemedian

MYSQL outputting median age from find median birth date


I'm finding the median age of employees in a database but instead of returning the birth date, i would like it to return the age. so essentially i want it to subtract the employee's birth year from the current year to get an age. So for example my result is shown as "2000-01-01" where id like it to be shown as "17". My code is as follows:

DROP PROCEDURE IF EXISTS medianAge;

DELIMITER //
CREATE PROCEDURE medianAge(OUT median DATE)
BEGIN
    DECLARE set_size INT(10);
    DECLARE median_index INT;
    DECLARE counter INT;
    DECLARE current_age DATE;
    DECLARE all_bdates CURSOR FOR
        SELECT bdate FROM employee ORDER BY bdate ASC;
    SET set_size = (SELECT COUNT(bdate) 
        FROM employee);
    SET median_index = FLOOR(set_size/2);
    SET counter = 0;
    OPEN all_bdates;
    WHILE counter < median_index DO
        FETCH all_bdates INTO current_age;
        SET counter = counter + 1;
    END WHILE;
    SET median = FLOOR(current_age);
END//
DELIMITER ;

CALL medianAge(@median);

SELECT @median;

Solution

  • SET median = FLOOR(DATEDIFF(now(), FLOOR(current_age)) / 365);