Karim, I overlooked at the following message:
Msg 147, Level 15, State 1, Line 28
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
This clearly shows the wrong implementation while using aggregate function. The code should be like this:
Select TOP 10 nDeviceId,
sDisplayName,
sgroupname,
sdescription,
dpolltime,
MIN(nused_min),
MAX(nused_max),
SUM(nsize),
(CAST(SUM(nused_min) AS FLOAT(2)) / CAST(SUM(nsize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) AS Inuse
from statisticalDisk
group by nDeviceId,
sDisplayName,
sgroupname,
sdescription, dpolltime
having (CAST(SUM(nused_min) AS FLOAT(2)) / CAST(SUM(nsize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) >= 80
Check this out and let me now if it works. Look out for using aggregate functions usage in BOL.