Getting Total of SUMs from current query

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)

Getting Total of SUMs from current query

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2011-07-12 : 12:12:43

I have a query that is close to what I need. However, I would like to get the TOTAL of the SUMs as seen below. Is this possible in the same query? In other words, I would like to get the total of the SUMs (3.765329) as another piece of data, such as TotalRuntime.

Current Query:
SELECT f_computername, f_application, SUM(f_runtime/60) AS Runtime
FROM tb_applicationusage
WHERE f_runtime IS NOT NULL AND f_computername = 'xxxxxxxx'
GROUP BY f_computername, f_application

Resultant Data:


f_computername	f_application	Runtime
xxxxxxxx	word		0.756500
xxxxxxxx	powerpoint	0.873665
xxxxxxxx	excel		0.932999
xxxxxxxx	iexplore	0.441166
xxxxxxxx	microsoftmath	0.760999

Thanks in advance,
Matt

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-07-12 : 12:51:05

[code]
SELECT f_computername, f_application
,SUM(f_runtime/60) OVER (PARTITION BY f_computername, f_application) AS Runtime
,SUM(f_runtime/60)) AS TotalRuntime
FROM tb_applicationusage
WHERE f_runtime IS NOT NULL AND f_computername = 'xxxxxxxx'
[/code]

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-12 : 12:58:57

Maybe this is sufficient?...


;with cte As (
	SELECT f_computername, f_application, SUM(f_runtime/60) AS Runtime
	FROM tb_applicationusage
	WHERE f_runtime IS NOT NULL AND f_computername = 'xxxxxxxx'
	GROUP BY f_computername, f_application
)
Select 
	*,
	TotalRuntime = (Select sum(Runtime) From cte)
From cte

Corey

I Has Returned!!

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2011-07-12 : 14:05:23

Thanks for the support guys. I'm getting errors through a copy/paste, but at least it gives me a place to start from.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-12 : 14:16:00

quote:

Originally posted by Ifor


SELECT f_computername, f_application
	,SUM(f_runtime/60) OVER (PARTITION BY f_computername, f_application) AS Runtime
	,SUM(f_runtime/60)) AS TotalRuntime
FROM tb_applicationusage
WHERE f_runtime IS NOT NULL AND f_computername = 'xxxxxxxx'

Ifor... did you test this? This will error out.

Corey

I Has Returned!!

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2011-07-12 : 15:54:24

Seventhnight, using your method is there a way to sort by Runtime DESC? If I attempt to add it to the query, the following error is generated:


The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Thanks,
Matt

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-12 : 15:55:42

quote:

Originally posted by Seventhnight

Maybe this is sufficient?...


;with cte As (
	SELECT f_computername, f_application, SUM(f_runtime/60) AS Runtime
	FROM tb_applicationusage
	WHERE f_runtime IS NOT NULL AND f_computername = 'xxxxxxxx'
	GROUP BY f_computername, f_application
)
Select 
	*,
	TotalRuntime = (Select sum(Runtime) From cte)
From cte
Order By Runtime desc

Corey

I Has Returned!!

Corey

I Has Returned!!

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2011-07-12 : 16:12:02

Ahhh, now I understand. I was attempting to add it under the "As" statement. Thank you very much for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources