在表中的行下面显示行的总和

xv8emn3q 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(373)

我有一张table

我有以下查询的结果集

select ss.Quantity as Qty ,ss.Item , ss.ES_ServiceStart as Time, fr.functionRoom from tblServiceSummary ss 
join servdesc sd on ss.Service_time =sd.ServDescID  
join funct fn on fn.FunctID = ss.FunctionID 
join tbl_functionRoom fr on fr.id=fn.EvtID
where fn.StartDate between '2018-09-19' and '2018-09-19' 
and ss.Item != ''  
order by ss.Item

这样地

我想表现得像

表示行数据和行的总和。
只是想知道这在mysql中是可能的吗？我无法实现与小组按功能划分。
我找到的唯一相关的东西是汇总函数。
我试过了
像这样的查询

SELECT Quantity, Item, count(Quantity) AS sumoption
FROM tblServiceSummary 
GROUP BY Quantity WITH ROLLUP

但这给了我这样的结果

也试过了

SELECT Quantity, Item, count(Quantity) AS sumoption
FROM tblServiceSummary 
GROUP BY Quantity WITH ROLLUP

请建议

mysql Aggregate rollup

来源：https://stackoverflow.com/questions/52256375/show-sum-of-rows-below-rows-in-table

1条答案

按热度按时间

kqhtkvqz1#

您可以使用“sum-group by item”选择并相应地排序来创建一个并集。

select '' as Kind, ss.Quantity as Qty, ss.Item as Item,
    ss.ES_ServiceStart as Time, fr.functionRoom
from tblServiceSummary ss 
join servdesc sd on ss.Service_time =sd.ServDescID  
join funct fn on fn.FunctID = ss.FunctionID 
join tbl_functionRoom fr on fr.id=fn.EvtID
where fn.StartDate between '2018-09-19' and '2018-09-19' 
and ss.Item != ''  

union

select 'SUBTOTAL', sum(ss.Quantity), ss.Item, '', ''
from tblServiceSummary ss 
join funct fn on fn.FunctID = ss.FunctionID 
where fn.StartDate between '2018-09-19' and '2018-09-19' 
and ss.Item != ''  
group by ss.Item

order by Item, Kind

赞(0）回复(0）举报 2021-06-21

我来回答

在表中的行下面显示行的总和

1条答案

相关问题

热门标签

最新问答