MYSQL 根据不同字段的汇总相同字段的总数

需求:
汇总一个用户不同支付方式的购买的总杯数
buy_num 杯数
pay_code 支付方式
pay_name 支付名称
pay_status 支付状态

ms_order 订单表
ms_user 用户表

SELECT u.id,pay_name,u.username,
sum(buy_num) as buy_num, 
count(IF(pay_code = "wxpay", true, null)) as wxpay_count, 
count(IF(pay_code = "alipay", true, null)) as alipay_count, 
sum(IF(pay_code = "wxpay", buy_num, 0)) as wxpay, 
sum(IF(pay_code = "alipay", buy_num, 0)) as alipay 
FROM `ms_user` `u` INNER JOIN `ms_order` `od` ON `od`.`user_id`=`u`.`id`  WHERE `pay_status` = 1 
GROUP BY `user_id`

通过sum(IF(pay_code = "XX", XXX, 0)) 根据不同字段增加汇总
通过count(IF(pay_code = "XX", true, null)) 根据不同字段汇总