Marikit Avendano Marikit Avendano - 17 days ago 6
MySQL Question

Get total value of the same column multiple rows and total of two different rows (mysql query)

I have a table "records"
[Records][1]

select typeofservice,
Coalesce(sum(incomecost,expensecost),0) 'incomecost,expensecost'
from records
where dateoftrans = '2016-11-22'
group by typeofservice with rollup;


I want to be able to have the result on the second image and this is my query... and all of this transactions happened on one day. Please help

https://i.stack.imgur.com/fLYav.png

Answer

If only for second image's result, you can try sum(incomecost) and sum(expensecost) like this:

SELECT
    typeofservice,
    Coalesce(SUM(incomecost), 0) AS incomecost,
    Coalesce(SUM(expensecost), 0) AS expensecost
FROM records
GROUP BY typeofservice
WITH ROLLUP