i need to get total of columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

i need to get total of columns

Author

Topic

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2009-01-04 : 12:59:45

i have a table structure like this

type B1 B2 B3 Total

can 0 0 1 1
kod 2 5 1 8
son 9 7 0 16

Tot 11 12 2 25

I am getting the total of the column 'Total' of each row, but how i can get the total of the row 'tot' of each column and the last value is 25 i.e, grand total, sum of total rows.

I need a query for that can any one help me on that

dev

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-04 : 13:07:40

use WITH ROLLUP clause after GROUP BY

http://www.java2s.com/Code/SQLServer/Analytical-Functions/WITHROLLUPWITHROLLUPaftertheGROUPBYstatement.htm

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-01-04 : 21:48:18

you can have it...

select sum(B1) B1, sum(B2) B2, sum(B3) B3, sum(Total) Total
from Table
where Type in ('cad','kod','son')

the output would be the grandtotal of columns B1,B2,B3 and Total for the type under cad,kod and son.

HTH :)

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-04 : 23:33:53

declare @temp table (type varchar(32), B1 int, B2 int, B3 int, Total int )
insert into @temp
select 'can', 0, 0, 1, 1 union all
select 'kod', 2, 5, 1, 8 union all
select 'son', 9, 7, 0, 16

--select * from @temp

select type,sum(B1) AS B1, sum(B2) AS B2, sum(B3) AS B3, sum(Total) AS Total
from @temp group by [type] with cube

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-05 : 02:17:29

If you use reporting service, do the totalling there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources