Search code examples
mysqlaggregaterollup

show sum of rows below rows in table

i have table tblservicesummary

enter image description here

I have a result set from following query

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

like this

I want to display like

means rows data and sum of rows altogether.

just want to know that is this possible in MySQL? I am not able to achieve with the group by function.

the only related thing I found is rollup function.

I tried

queries like 

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

but that gives me result like

enter image description here

also tried 

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

please suggest

Solution

  • You could make a union with a "sum - group by item" select and order correspondingly.

    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