如何按Household ID分组统计项目注册的儿童与成人客户数量？

你可以用条件聚合实现需求，不需要复杂子查询，直接在原分组查询基础上添加统计逻辑即可，这是处理这类分组子集统计的常用方法。

方法一：SUM + CASE WHEN #

SELECT 
    household.id AS household_id,
    COUNT(clients.id) AS total_clients,
    SUM(CASE WHEN clients.age < 18 THEN 1 ELSE 0 END) AS child_count,
    SUM(CASE WHEN clients.age >= 18 THEN 1 ELSE 0 END) AS adult_count
FROM enrollments
INNER JOIN clients ON enrollments.ref_client = clients.id
INNER JOIN household ON enrollments.ref_household = household.id
GROUP BY household.id;

• 原理：对每个家庭的客户，用CASE WHEN判断年龄，符合条件返回1，否则返回0，通过SUM累加得到对应分组的儿童/成人数量
• 如果clients.age存在NULL值，建议在条件里加上AND clients.age IS NOT NULL，避免NULL被误统计

方法二：COUNT + CASE WHEN #

COUNT函数会自动忽略NULL值，也可以用这种更简洁的写法：

SELECT 
    household.id AS household_id,
    COUNT(clients.id) AS total_clients,
    COUNT(CASE WHEN clients.age < 18 THEN 1 END) AS child_count,
    COUNT(CASE WHEN clients.age >= 18 THEN 1 END) AS adult_count
FROM enrollments
INNER JOIN clients ON enrollments.ref_client = clients.id
INNER JOIN household ON enrollments.ref_household = household.id
GROUP BY household.id;

• 原理：当年龄不符合条件时，CASE返回NULL，不会被COUNT计入，最终得到对应分组的目标客户数

关于你之前的问题 #

你之前的子查询没有关联外层查询的household.id，导致计算的是全局范围内的儿童/成人总数，而非每个家庭的子集数量。用条件聚合可以在同一个分组查询中完成所有统计，逻辑更清晰，执行效率也更高。

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