对具有相同属性的两个条件使用sum

Question

当我创建一个表来统计18-40岁之间的用户时，我需要在sum((age>=18 and age<=40) and (Gender='M'))这样的表达式中有两个关于BirthYear的条件，但这个条件的返回总是等于0，整个查询和输出如下，(MySQL 5.30(

create table AgeUser
select
Id as 'Id',
sum((age<18) and (Gender='M')) as 'MaleUsersUnder18',
sum((age>=18 and age<=40) and (Gender='M')) as 'MaleUsers18To40',
sum((age>40) and (Gender='M')) as 'MaleUsersOver40',
sum((age<18) and (Gender='F')) as 'FemaleUsersUnder18',
sum((age>=18 and age<=40) and (Gender='F')) as 'FemaleUsers18To40',
sum((age>40) and (Gender='F')) as 'FemaleUsersOver40'
from User group by Id;

id  MUUnder18  MU18To40  MUOver40  FUUnder18  FU18To40  FUOver40
72      2137     0         1316     645         0         123
79      2613     0         1616     1064        0         676
82      592      0         363      203         0         554

用户表示例Id表示服务站Id

Id  userid  Name  age   gender     UserType
72    12     L     18     M        customer

如何修复查询？

Answer 1

最佳总和

sum(case when age<18 and Gender='M' then 1 else 0 end) as 'MaleUsersUnder18',

等等。。