mysql - 获取同一列多行的总值和两行不同的总和(mysql查询)

Question

我有一个表“记录”[记录][1]

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

我希望能够在第二张图片上获得结果，这是我的查询...所有这些交易都发生在一天之内。请帮忙

/image/fLYav.png

Answer 1

如果仅针对第二张图片的结果，您可以像这样尝试 sum(incomecost) 和 sum(expensecost):

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