同列分类求和并按其他列分组 sqlalchemy 和 SQL

同列分类求和并按其他列分组

问题描述：如上表 INCOME，要分别统计每个用户微信、支付宝的收入总和，结果格式如下。(即按 微信、支付宝分类，按照userid分group求和)

//sqlalchemy
db.session.query(INCOME.userid,
func.sum(case([(INCOME.type=='微信',INCOME.income)],else_=0)).label('income_wx'),
func.sum(case([(INCOME.type=='支付宝',INCOME.income)],else_=0)).label('income_ali'),
).group_by(INCOME.userid).all()

//sql 
SELECT userid,
    SUM(CASE WHEN type='微信' THEN income ELSE 0 END) AS income_wx,
    SUM(CASE WHEN type='支付宝' THEN income ELSE 0 END) AS income_ali
FROM INCOME GROUP BY userid