How to show sum of columns in new top row? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to show sum of columns in new top row?

Author

Topic

anujpratap84
Starting Member

45 Posts

Posted - 2011-09-28 : 02:56:29

Hi All,

I have a table with some no of columns.
I want to inert a row on top of the table witch contains the sum of each column.
Ex:

Month DEF TDN CLX
1 23 55 6
2 22 5 4

The resultant table will be like

Month DEF TDN CLX
Total 45 60 10
1 23 55 6
2 22 5 4

Please help
Thanks in advance

Anuj Pratap Singh

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-28 : 03:16:00

Make use of ROLLUP in SQL Server.

Best Regards
Vadivel

http://vadivel.blogspot.com

anujpratap84
Starting Member

45 Posts

Posted - 2011-09-28 : 03:21:01

Thanks for quick reply..
Can you give an example based on my senerio..

quote:
Originally posted by vmvadivel

Make use of ROLLUP in SQL Server.

Best Regards
Vadivel

http://vadivel.blogspot.com

Anuj Pratap Singh

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-28 : 06:22:35

Create table tblTest11
(
[Month] int,
DEF int,
TDN int,
CLX int
)
GO

INSERT INTO tbltest11
SELECT 1, 23, 55, 6 UNION ALL
SELECT 2, 22, 5, 4
GO

--Solution
SELECT
ISNULL(CONVERT(VARCHAR,[Month]),'Total') AS [Month],
CONVERT(VARCHAR,SUM(DEF),1) AS [DEF],
CONVERT(VARCHAR,SUM(TDN),1) AS [TDN],
CONVERT(VARCHAR,SUM(CLX),1) AS [CLX]
FROM
tblTest11
GROUP BY
[Month] WITH ROLLUP
ORDER BY
[Month]

Best Regards
Vadivel

http://vadivel.blogspot.com

anujpratap84
Starting Member

45 Posts

Posted - 2011-09-29 : 01:43:19

Thanks a alot..
It's working....

quote:
Originally posted by vmvadivel

Create table tblTest11
(
[Month] int,
DEF int,
TDN int,
CLX int
)
GO

INSERT INTO tbltest11
SELECT 1, 23, 55, 6 UNION ALL
SELECT 2, 22, 5, 4
GO

--Solution
SELECT
ISNULL(CONVERT(VARCHAR,[Month]),'Total') AS [Month],
CONVERT(VARCHAR,SUM(DEF),1) AS [DEF],
CONVERT(VARCHAR,SUM(TDN),1) AS [TDN],
CONVERT(VARCHAR,SUM(CLX),1) AS [CLX]
FROM
tblTest11
GROUP BY
[Month] WITH ROLLUP
ORDER BY
[Month]

Best Regards
Vadivel

http://vadivel.blogspot.com

Anuj Pratap Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources