May 19, 2003 at 10:22 am
I use the following query to get top 20 logins that eat most CPU resources. I need to change it, so I get 21st row as a SUM of sum([CPU]) of the rest of logins. Is it possible to do so?
SELECT top 20 [LoginName], sum([CPU]) as SUM_CPU
FROM [master].[dbo].[proc_use]
GROUP BY [LoginName]
ORDER BY SUM_CPU desc
May 19, 2003 at 11:10 am
How about something like this:
select * from (SELECT top 20 [LoginName], sum([CPU]) as SUM_CPU
FROM [master].[dbo].[proc_use]
GROUP BY [LoginName]
order by Sum_cpu DESC
) B
union
select 'Others' 'LoginName', (select sum([CPU]) FROM [master].[dbo].[proc_use]) -
(select sum(sum_x) from (SELECT top 20 sum([CPU]) as sum_x
FROM [master].[dbo].[proc_use] GROUP BY [LoginName] order by Sum_x desc )a) as sum_cpu
Order by sum_cpu DESC
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
May 19, 2003 at 11:14 am
You could try:
SELECT ISNULL(dt.LoginName, 'Rest of Group') , SUM(m.[CPU]) as SUM_CPU_M
FROM [master].[dbo].[proc_use] m
LEFT JOIN
(
SELECT top 20 [LoginName], sum([CPU]) as SUM_CPU
FROM [master].[dbo].[proc_use]
GROUP BY [LoginName]
ORDER BY m.SUM_CPU desc
) as dt
ON m.LoginName = dt.LoginName
ORDER BY SUM_CPU_M ASC
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply