Calculate percentage 1

[TitleistDBA]

I have the following query on a database for Altiris.
it returns the device name, total space and free spcace for all of our servers in the company.

SELECT LD.[DEVICE ID],
SUM(LD.[Size in MBytes]) AS 'Total Space',
SUM(LD.[Free Space in MBytes]) AS 'Free Space'
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'
GROUP BY LD.[DEVICE ID]
ORDER BY LD.[DEVICE ID]

The result set looks like this.

DEVICE ID TOTAL SPACE FREEE SPACE
----------- -------------- -----------
A: 0 0
C: 6250963 4518721
D: 26141720 15533886

I need to caclulate the percentage of used disk space.

Do I divide the SUM of total space by the sum of free space to get that?

SELECT SUM(LD.[Size in MBytes])/
SUM(LD.[Free Space in MBytes]) AS 'Percent Used'

Is that correct?
Thanks

http://www.acushnetcompany.com

http://www.titleist.com

 

[SQLDenis]

it would be freespace/size * 100

also make sure you convert to decimal first

select (4518721.0/6250963.0) * 100.00

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[JBaileys]

Titleist,

Impressive query. Are you looking for free space percentage per machine or for all machines.

The used space is total space - free space.
To obtain the percentage take the used space / total space.

select sum(ld.[size in MBytes]-ld.[free space in mbytes])/sum(ld.[size in mbytes])

 

[TitleistDBA]

When I try to run that I get
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'SUM'.

This is what I have.

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.[Free Space in MBytes])/SUM(LD.[Size in MBytes])*100.00 AS 'Percent Used'

http://www.acushnetcompany.com

http://www.titleist.com

 

[TitleistDBA]

This query is returning me disk information about all machines. We are going to use the results to halp design our SAN.

http://www.acushnetcompany.com

http://www.titleist.com

 

[SQLDenis]

you are missing a comma after AS 'Free Space in MB'

also you have some extra spaces here .[Size in MBytes])*

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[TitleistDBA]

I feel stupid!
This is my result set now?

C: 6250963 4518721 .00
D: 26141720 15533886 .00
E: 4830965 3398903 .00
F: 11119681 6033662 .00
G: 1686799 972264 .00
H: 1012356 722261 .00
I: 406197 48508 .00
J: 657157 348851 .00
Any Idea why the percentage is 0?

http://www.acushnetcompany.com

http://www.titleist.com

 

[SQLDenis]

yes, like I said befor you need to convert to decimal

look at these 2 queries

select (convert(decimal(20,2),4518721)/convert(decimal(20,2),6250963.0)) * 100.00

select (4518721/6250963) * 100.00

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[TitleistDBA]

I got it!..I forgot to convert the fields to decicmals.

Thanks for your help.
Here is my final query!.

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]-ld.[free space in mbytes])/sum(ld.[size in mbytes]) AS 'Percent Used'
SUM(CONVERT(DECIMAL,LD.[Free Space in MBytes]))/SUM(CONVERT(DECIMAL,LD.[Size in MBytes]))*100.00 AS 'Percent Used'
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]

and results.

C: 6250963 4518721 72.288300
D: 26141720 15533886 59.421800
E: 4830965 3398903 70.356600
F: 11119681 6033662 54.261100
G: 1686799 972264 57.639500
H: 1012356 722261 71.344500
I: 406197 48508 11.941900
J: 657157 348851 53.084800

Thanks for your help!

http://www.acushnetcompany.com

http://www.titleist.com

 

[SQLDenis]

np and tx for the star

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/