percentage calculation - 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

percentage calculation

Author

Topic

keilamym
Starting Member

3 Posts

Posted - 2010-07-19 : 22:58:54

any assistance would be appreciated... i have this sql query that works but im trying to figure out how to calculate the percentage in SQL instead of exporting it into excel..

select
(select  COUNT (*)  from  dbo.V_R_System inner join  dbo.v_GS_OPERATING_SYSTEM on   dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.V_R_System.ResourceId where   dbo.v_GS_OPERATING_SYSTEM.Caption0 = 'Microsoft Windows XP Professional' and   dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 < 'Service Pack 3' ) as PATCHED,
(select  COUNT (*) AS Upgraded from  dbo.V_R_System inner join  dbo.v_GS_OPERATING_SYSTEM on   dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.V_R_System.ResourceId where   dbo.v_GS_OPERATING_SYSTEM.Caption0 = 'Microsoft Windows XP Professional' and   dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 = 'Service Pack 3') as UNPATCHED

again any assistance is appreciated.

Devart
Posting Yak Master

102 Posts

Posted - 2010-07-20 : 02:28:51

Hello,

For example:

select
(select COUNT (*) from dbo.V_R_System inner join dbo.v_GS_OPERATING_SYSTEM on dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.V_R_System.ResourceId where dbo.v_GS_OPERATING_SYSTEM.Caption0 = 'Microsoft Windows XP Professional' and dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 < 'Service Pack 3' ) as PATCHED,
(select COUNT (*) AS Upgraded from dbo.V_R_System inner join dbo.v_GS_OPERATING_SYSTEM on dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.V_R_System.ResourceId where dbo.v_GS_OPERATING_SYSTEM.Caption0 = 'Microsoft Windows XP Professional' and dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 = 'Service Pack 3') as UNPATCHED,

100*(select COUNT (*) from dbo.V_R_System inner join dbo.v_GS_OPERATING_SYSTEM on dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.V_R_System.ResourceId where dbo.v_GS_OPERATING_SYSTEM.Caption0 = 'Microsoft Windows XP Professional' and dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 < 'Service Pack 3' )/
(select COUNT (*) from dbo.V_R_System inner join dbo.v_GS_OPERATING_SYSTEM on dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.V_R_System.ResourceId where dbo.v_GS_OPERATING_SYSTEM.Caption0 = 'Microsoft Windows XP Professional') as PROC_PATCHED,
100*(select COUNT (*) AS Upgraded from dbo.V_R_System inner join dbo.v_GS_OPERATING_SYSTEM on dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.V_R_System.ResourceId where dbo.v_GS_OPERATING_SYSTEM.Caption0 = 'Microsoft Windows XP Professional' and dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 = 'Service Pack 3')/
(select COUNT (*) AS Upgraded from dbo.V_R_System inner join dbo.v_GS_OPERATING_SYSTEM on dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.V_R_System.ResourceId where dbo.v_GS_OPERATING_SYSTEM.Caption0 = 'Microsoft Windows XP Professional') as PROC_UNPATCHED

Best regards,

Devart Team

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-20 : 02:45:31

[code]
SELECT PATCHED, UNPATCHED, TOTAL,
PATCHED_PERCENT = PATCHED * 100.0 / TOTAL,
UNPATCHED_PERCENT = UNPATCHED * 100.0 / TOTAL
FROM
(
SELECT COUNT (CASE WHEN dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 < 'Service Pack 3' THEN 1 END) AS PATCHED,
COUNT (CASE WHEN dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 = 'Service Pack 3' THEN 1 END) AS UNPATCHED,
COUNT (*) AS TOTAL
FROM dbo.V_R_System
INNER JOIN dbo.v_GS_OPERATING_SYSTEM ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.V_R_System.ResourceId
WHERE dbo.v_GS_OPERATING_SYSTEM.Caption0 = 'Microsoft Windows XP Professional'
) AS D
[/code]

KH
[spoiler]Time is always against us[/spoiler]

keilamym
Starting Member

3 Posts

Posted - 2010-07-20 : 11:14:17

thanks for the responses.. i'll try it and let you know how it goes..

thanks again..

keilamym
Starting Member

3 Posts

Posted - 2010-07-20 : 12:02:13

they both work... i think i need to go purchase a sql book cause you 2 are awesome...

thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources