mysql - mysql查询行中的总和值

Question

是否可以将此查询中各行的值相加？我大胆地提出了自己的想法，但行不通。

SELECT 
    `view_monthly_bills`.`pay_id` AS `pay_id`,
    `view_monthly_bills`.`pay_month` AS `pay_month`,
    GROUP_CONCAT((CASE
            WHEN (`view_monthly_bills`.`bill_type` = 'Mortgage') THEN `view_monthly_bills`.`debit`
            ELSE NULL
        END)
        SEPARATOR ',') AS `Mortgage`,
    GROUP_CONCAT((CASE
            WHEN (`view_monthly_bills`.`bill_type` = 'Shopping') THEN `view_monthly_bills`.`debit`
            ELSE NULL
        END)
        SEPARATOR ',') AS `Shopping`,
    GROUP_CONCAT((CASE
            WHEN (`view_monthly_bills`.`bill_type` = 'TV') THEN `view_monthly_bills`.`debit`
            ELSE NULL
        END)
        SEPARATOR ',') AS `TV`,
        **SUM( Mortgage + Shopping + TV)**
FROM
    `view_monthly_bills`
GROUP BY `view_monthly_bills`.`pay_id` , `view_monthly_bills`.`pay_month`
ORDER BY `view_monthly_bills`.`pay_id` DESC
LIMIT 6

Answer 1

尝试使用case语句与OR和函数聚合SUM:

SELECT 
    `view_monthly_bills`.`pay_id` AS `pay_id`,
    `view_monthly_bills`.`pay_month` AS `pay_month`,
    GROUP_CONCAT((CASE
            WHEN (`view_monthly_bills`.`bill_type` = 'Mortgage') THEN `view_monthly_bills`.`debit`
            ELSE NULL
        END)
        SEPARATOR ',') AS `Mortgage`,
    GROUP_CONCAT((CASE
            WHEN (`view_monthly_bills`.`bill_type` = 'Shopping') THEN `view_monthly_bills`.`debit`
            ELSE NULL
        END)
        SEPARATOR ',') AS `Shopping`,
    GROUP_CONCAT((CASE
            WHEN (`view_monthly_bills`.`bill_type` = 'TV') THEN `view_monthly_bills`.`debit`
            ELSE NULL
        END)
        SEPARATOR ',') AS `TV`,
        SUM((CASE
            WHEN (`view_monthly_bills`.`bill_type` = 'TV' OR
                `view_monthly_bills`.`bill_type` = 'Shopping' OR
                `view_monthly_bills`.`bill_type` = 'Mortgage'
                )THEN `view_monthly_bills`.`debit`
            ELSE 0
        END)) as sum_debit
FROM
    `view_monthly_bills`
GROUP BY `view_monthly_bills`.`pay_id` , `view_monthly_bills`.`pay_month`
ORDER BY `view_monthly_bills`.`pay_id` DESC
LIMIT 6