Возрастной диапазон MySQL GROUP BY, включая нулевые диапазоны

Я пытаюсь подсчитать количество людей по возрастным диапазонам, и я могу почти сделать это с двумя проблемами:

Если в данном возрастном диапазоне нет людей (NULL), то этот возрастный диапазон не отображается в результатах. Например, в моих данных нет записей для "Более 80", поэтому диапазон дат не отображается. В принципе, это выглядит ошибкой в программировании, когда отсутствуют диапазоны дат.
Я хотел бы заказать результаты определенным образом. В запросе ниже, потому что ORDER BY по age_range, результаты для '20 - 29 'идут до результатов для "Менее 20".

Здесь образец таблицы "запросы" db:

inquiry_id  birth_date
1           1960-02-01
2           1962-03-04
3           1970-03-08
4           1980-03-02
5           1990-02-08

Здесь запрос:

SELECT
    CASE
        WHEN age < 20 THEN 'Under 20'
        WHEN age BETWEEN 20 and 29 THEN '20 - 29'
        WHEN age BETWEEN 30 and 39 THEN '30 - 39'
        WHEN age BETWEEN 40 and 49 THEN '40 - 49'
        WHEN age BETWEEN 50 and 59 THEN '50 - 59'
        WHEN age BETWEEN 60 and 69 THEN '60 - 69'
        WHEN age BETWEEN 70 and 79 THEN '70 - 79'
        WHEN age >= 80 THEN 'Over 80'
        WHEN age IS NULL THEN 'Not Filled In (NULL)'
    END as age_range,
    COUNT(*) AS count

    FROM (SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM inquiries) as derived

    GROUP BY age_range

    ORDER BY age_range

Здесь простое решение, основанное на предложении Wrikken:

SELECT
    SUM(IF(age < 20,1,0)) as 'Under 20',
    SUM(IF(age BETWEEN 20 and 29,1,0)) as '20 - 29',
    SUM(IF(age BETWEEN 30 and 39,1,0)) as '30 - 39',
    SUM(IF(age BETWEEN 40 and 49,1,0)) as '40 - 49',
    SUM(IF(age BETWEEN 50 and 59,1,0)) as '50 - 59',
    SUM(IF(age BETWEEN 60 and 69,1,0)) as '60 - 69',
    SUM(IF(age BETWEEN 70 and 79,1,0)) as '70 - 79',
    SUM(IF(age >=80, 1, 0)) as 'Over 80',
    SUM(IF(age IS NULL, 1, 0)) as 'Not Filled In (NULL)'

FROM (SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM inquiries) as derived

Ответ 1

Альтернативой таблице диапазонов (которая имеет мои предпочтения), однострочный ответ может быть:

SELECT
    SUM(IF(age < 20,1,0)) as 'Under 20',
    SUM(IF(age BETWEEN 20 and 29,1,0)) as '20 - 29',
    SUM(IF(age BETWEEN 30 and 39,1,0)) as '30 - 39',
    SUM(IF(age BETWEEN 40 and 49,1,0)) as '40 - 49',
...etc.
FROM inquiries;

Ответ 2

Один из способов упорядочения результатов - ввести столбец в выражении select и присвоить ему значение ранга того, как вы хотите, чтобы ваши результаты упорядочивались вместе с остальными, а затем упорядочивались по этой строке, например

SELECT
CASE
    WHEN age < 20 THEN 'Under 20'
    WHEN age BETWEEN 20 and 29 THEN '20 - 29'
    WHEN age BETWEEN 30 and 39 THEN '30 - 39'
    WHEN age BETWEEN 40 and 49 THEN '40 - 49'
    WHEN age BETWEEN 50 and 59 THEN '50 - 59'
    WHEN age BETWEEN 60 and 69 THEN '60 - 69'
    WHEN age BETWEEN 70 and 79 THEN '70 - 79'
    WHEN age >= 80 THEN 'Over 80'
    WHEN age IS NULL THEN 'Not Filled In (NULL)'
END as age_range,
COUNT(*) AS count,
 CASE
    WHEN age < 20 THEN 1
    WHEN age BETWEEN 20 and 29 THEN 2
    WHEN age BETWEEN 30 and 39 THEN 3
    WHEN age BETWEEN 40 and 49 THEN 4
    WHEN age BETWEEN 50 and 59 THEN 5
    WHEN age BETWEEN 60 and 69 THEN 6
    WHEN age BETWEEN 70 and 79 THEN 7
    WHEN age >= 80 THEN 8
    WHEN age IS NULL THEN 9
END as ordinal

FROM (SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM inquiries) as derived

GROUP BY age_range

ORDER BY ordinal

Ответ 3

Создайте таблицу, содержащую все диапазоны и использующую внешнее соединение.
Заказ по числовому значению в другом столбце этой таблицы

Диапазон SELECT,.... Диапазоны FROM LEFT JOIN (ваш подзапрос) ON (диапазоны .range = your_range) ... ORDER BY range.year ASC