Calculate percentage 1

[TitleistDBA]

I have the following query that calculates Total disk space and percent free for all of our servers. My boss has asked me to add the Percent used to the query.

When I try the following I don't get the correct value for Percent Used.

SELECT LD.[DEVICE ID],
SUM(LD.[Size in MBytes]) AS 'Total Space in MB',
SUM(LD.[Free Space in MBytes]) AS 'Free Space in MB',
SUM(LD.[Size in MBytes])-SUM(LD.[Free Space in MBytes]) AS 'MB USED',
SUM(CONVERT(DECIMAL,LD.[Size in MBytes]))-SUM(CONVERT(DECIMAL,LD.[Free Space in MBytes]))/SUM(CONVERT(DECIMAL,LD.[Size in MBytes]))*100.00 AS 'Percent Used',
SUM(CONVERT(DECIMAL,LD.[Free Space in MBytes]))/SUM(CONVERT(DECIMAL,LD.[Size in MBytes]))*100.00 AS 'Percent FREE'
FROM ( [vResourceEx] T0 INNER JOIN [Inv_AeX_OS_Operating_System] T1
ON T0.[Guid] = T1.[_ResourceGuid] ) INNER JOIN [Inv_AeX_AC_Identification] T2
ON T1.[_ResourceGuid] = T2.[_ResourceGuid]
INNER JOIN [INV_AEX_HW_LOGICAL_DISK] LD
ON T2.[_ResourceGuid] = LD.[_ResourceGuid]
WHERE ((NOT T1.[OS Name] = 'Microsoft Windows XP'
AND (NOT T1.[Product ID] LIKE '51873%'
AND NOT T1.[Product ID] LIKE '51874%'))
AND NOT T2.[OS Type] = 'Workstation')
AND substring(LD.[DEVICE ID],1,1) < 'K'
AND substring(LD.[DEVICE ID],1,1) !='A'
GROUP BY LD.[DEVICE ID]
ORDER BY LD.[DEVICE ID]


Here are the results I currently get.

C: 6250963 4518721 1732242 6250891 72.288300
D: 26141720 15533886 10607834 26141661 59.421800
E: 4830965 3398903 1432062 4830895 70.356600
F: 11119681 6033662 5086019 11119627 54.261100
G: 1686799 972264 714535 1686741 57.639500
H: 1012356 722261 290095 1012285 71.344500
I: 406197 48508 357689 406185 11.941900
J: 657157 348851 308306 657104 53.084800

So for the value of C: I should get 27% not 6250891?

Any ideas?

http://www.acushnetcompany.com

http://www.titleist.com

 

[vongrunt]

Huh?

5th column for c: says 72.28... isn't that approximately 100 - 27?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

you are looking at the wrong data

select (4518721.0/6250963.0) * 100

C: 6250963 4518721 1732242 6250891 72.288300

the last column (72.2883000) is correct

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[TitleistDBA]

Yes it is..But my boss would rather see the 27% So once I get the math to work for that percentage I will remove the percent free.

http://www.acushnetcompany.com

http://www.titleist.com

 

[SQLDenis]

so then do something like this

select 100.0 -((4518721.0/6250963.0) * 100)

100.0 -(SUM(CONVERT(DECIMAL,LD.[Free Space in MBytes]))/SUM(CONVERT(DECIMAL,LD.[Size in MBytes]))*100.00) AS 'Percent FREE'


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[TitleistDBA]

SQLDenis That worked!!!
Thanks for your help!!

http://www.acushnetcompany.com

http://www.titleist.com