SQL Query to get the sum of all the column values ​​in the last row of the result set along with the sum of the rows (by groups)

Question

Can someone please help me write a request for TCS and TRS?

ID Jan Feb Mar TRS 1 4 5 6 15 2 5 5 5 15 3 1 1 1 3 TCS 10 11 12 

Both TCS (Total Column Sum) and TRS (Total Row Sum) are the new column and row, respectively, that give them.

Answer 1

You can use GROUP BY and WITH ROLLUP , for example:

 SELECT id , SUM(jan) as jan , SUM(feb) as feb , SUM(mar) as mar , SUM(jan+feb+mar) as TRS FROM test GROUP BY id WITH ROLLUP 

Live demo on sqlfiddle.

Answer 2

This request will complete the task

 select cast(id as varchar(20)), Jan, Feb, Mar , Jan + Feb + Mar as TRS from table1 union all select 'TCS' as id, SUM(Jan) Jan, SUM(Feb) Feb, SUM(Mar) Mar, null as TRS from table1 

The first column will be returned as varchar , since you have a combination of integers (id) and TCS text.

Answer 3

select Sum (Jan + Feb + Mar) as TRS