Howe to get Grand totals? - SQL Server Forums

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

Howe to get Grand totals?

Author

Topic

LuxuryYacht
Starting Member

1 Post

Posted - 2007-09-24 : 16:01:54

Hi,

Can you get grand totals row in sql?

I want to add val column to a grant total, Time1 to a grand total and time2 to a grand total in table below. How to do this please?

P Val TIME1 TIME2

p1 0 1900-01-01 00:49:59.000 1900-01-01 00:00:00.000
p2 22194 1900-01-01 06:33:13.000 1900-01-01 00:26:55.000
p3 22974 1900-01-01 06:33:13.000 1900-01-01 00:52:39.000

Thank you!

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 01:37:14

[code]
CREATE TABLE #TEMP
(
P varchar(10) NOT NULL,
Val int NOT NULL,
TIME1 datetime NOT NULL,
TIME2 datetime NOT NULL
)

INSERT INTO #TEMP
SELECT 'p1', 0, '1900-01-01 00:49:59.000', '1900-01-01 00:00:00.000' UNION ALL
SELECT 'p2', 22194, '1900-01-01 06:33:13.000', '1900-01-01 00:26:55.000' UNION ALL
SELECT 'p3', 22974, '1900-01-01 06:33:13.000', '1900-01-01 00:52:39.000'

SELECT *
FROM #TEMP
UNION
SELECT 'Total',
SUM(Val),
DATEADD(Second, SUM(((DATEPART(HOUR, TIME1)*60+DATEPART(MINUTE, TIME1))*60)+DATEPART(SECOND, TIME1)), 0),
DATEADD(Second, SUM(((DATEPART(HOUR, TIME2)*60+DATEPART(MINUTE, TIME2))*60)+DATEPART(SECOND, TIME2)), 0)
FROM #TEMP

DROP TABLE #TEMP
[/code]
Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources