How to get the Sum of all column values in the last row of a resultset?

I need to get the sum of all column values of a result set in the last row.

Here is my SQL query.

select Master_Code, SUM(Jan), SUM(Feb), SUM(Mar)
from dbo.foobar
WHERE Participating_City = 'foofoo'
GROUP BY Master_Code ORDER BY Master_Code ASC

somethin开发者_Python百科g like this:

    Master_Code Jan Feb Mar 
    1            4   5   6
    2            5   5   5
    Total        9  10  11

Make a union where you repeat the same query but without the grouping:

select Title, Jan, Feb, Mar
from (
  select Master_Code as Title, SUM(Jan) as Jan, SUM(Feb) as Feb, SUM(Mar) as Mar
  from dbo.foobar
  WHERE Participating_City = 'foofoo'
  GROUP BY Master_Code ORDER BY Master_Code ASC
) x
union all
select 'Total', SUM(Jan) as Jan, SUM(Feb) as Feb, SUM(Mar) as Mar
from dbo.foobar
WHERE Participating_City = 'foofoo'

Assuming there are no null master_code rows.

SELECT ISNULL(Master_code, 'Total') AS Master_Code,
       Jan,
       Feb,
       Mar
FROM (
      SELECT Master_code,
             SUM(Jan) AS Jan,
             SUM(Feb) AS Feb,
             SUM(Mar) AS Mar
      FROM foobar
      WHERE Participating_City = 'foofoo'
      GROUP BY Master_code WITH ROLLUP
     ) AS DT

You can also use Coalesce and With Rollup.

SELECT COALESCE(Master_Code, 'TOTAL') AS MASTER_CODE, SUM(Jan), SUM(Feb), SUM(Mar)
FROM dbo.foobar
WHERE Participating_City = 'foofoo'
GROUP BY Master_Code WITH ROLLUP
ORDER BY Master_Code DESC

select * 
into #temporder from 
(
    SELECT DegreeType,CollegeName,Degree,subjects,Male_Female,GEN,EWS,OBC,SC,ST,SPD,SPD1,SPDWH,Total FROM #tempfinal                                    

    UNION all

    SELECT '' DegreeType,'Total' collegename,'' Degree,'' subjects,'' Male_Female,SUM(GEN) GEN,sum(EWS) EWS
    ,SUM(OBC) OBC,SUM(SC) SC,SUM(st) st,SUM(SPD) SPD,SUM(SPD1) SPD1,SUM(SPDWH) SPDWH,sum(total) total FROM 
        (
            SELECT *FROM #tempfinal
        ) A
        
) D