MySQL GROUP BY年龄范围，包括空范围 [英] MySQL GROUP BY age range including null ranges

问题描述

我正在尝试按年龄段计算人数，我几乎可以解决两个问题:

I'm trying to count the number of people by age ranges, and I can almost do it with 2 problems:

1. 如果在给定的年龄范围(NULL)中没有人，则该年龄范围不会出现在结果中.例如，在我的数据中没有超过80"的条目，因此不会出现日期范围.基本上，当缺少日期范围时，在编程中看起来像是一个错误.

1. If there are no people in a given age range (NULL), then that age range does not appear in the results. For example, in my data there's no entries for "Over 80" so that date range does not appear. Basically, it looks like a mistake in the programming when there are missing date ranges.

我想以特定方式对结果进行排序.在下面的查询中，由于ORDER BY在age_range的范围内，因此"20-29"的结果要早于"20岁以下"的结果.

I'd like to order the results in a specific way. In the query below, because the ORDER BY is by age_range, the results for '20 - 29' come before the results for 'Under 20'.

以下是数据库表查询"的示例:

Here's a sample of the db table "inquiries":

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的建议的简单解决方案:

Here's a simple solution based on the suggestion by 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

推荐答案

范围表的另一种选择(我偏爱)，单行答案可能是:

An alternative to the range table (which has my preference), a single-row answer could be:

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;

这篇关于MySQL GROUP BY年龄范围，包括空范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！