Lars7 Lars7 -4 years ago 130
MySQL Question

sum values in rows mysql query

Is it possible to sum the values from the indiviual rows in this query?
I've made bold the idea I had but it doesn't work.

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 Source

try use case statement with OR and function agregation 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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download