如何在SQL的GROUP BY子句中去除NULL及无效值

你的分组查询结果里出现了空值（NULL）和?这类不符合预期的分组，解决方法是在GROUP BY之前用WHERE子句过滤掉这些无效数据，确保只有有效数据参与分组。

修改后的SQL代码如下：

SELECT 
    age, primary_fur_colour, location,
    SUM(CASE WHEN running = 'True' THEN 1 ELSE 0 END) AS total_running,
    SUM(CASE WHEN chasing = 'True' THEN 1 ELSE 0 END) AS total_chasing,
    SUM(CASE WHEN climbing = 'True' THEN 1 ELSE 0 END) AS total_climbing,
    SUM(CASE WHEN eating = 'True' THEN 1 ELSE 0 END) AS total_eating,
    SUM(CASE WHEN foraging = 'True' THEN 1 ELSE 0 END) AS total_foraging
FROM squirrel_data
-- 过滤掉无效的分组字段值
WHERE 
    age IS NOT NULL 
    AND age != '?'
    AND primary_fur_colour IS NOT NULL
    AND location IS NOT NULL
GROUP BY age, primary_fur_colour, location

过滤逻辑说明： #

• 针对age字段：同时排除NULL值和?符号，这两个都是你不需要的分组依据
• 针对primary_fur_colour和location字段：排除NULL空值，只保留有明确值的记录

这样处理后，查询结果就只会保留像Adult/Cinnamon/Ground Plane、Juvenile/Gray/Above Ground这类有效的分组，那些包含空值或?的无效分组会被直接过滤掉。

内容的提问来源于stack exchange，提问作者Ami