Important Notice: On February 29th, this community was put into read-only mode. All existing posts will remain but customers are unable to add new posts or comment on existing. Please feel to join our Community Discord for any questions and discussions.

Calculated report column displaying 0 zero

Hi,

I'm trying to run a report do display the available disc space for machines which is mostly working however i have a calculated column("Percent available") that i want to show a figure to display the percentage of available space. It seems not matter what i try i keep getting a zero 0.

In hoping something can point out something stupid i have missed.

 

This is what i have tried have tried:

  • Simplifying the code to: LogicalDisks.FreeSpace / LogicalDisks.Size
  • Multiplying the amount by 100 in case it doesn't want to display decimal figures
  • Casting the code as varchar to try and get around rounding issues: cast((((LogicalDisks.FreeSpace / 1024 / 1024 / 1024 ) / (LogicalDisks.Size / 1024 / 1024 / 1024)) * 100) as varchar(50)) as "Percent available"

 

Please see my report code below:

SELECT
Computers.Name AS "Computer Name"
,case when Computers.IsOnline=1 then 'Online' else 'Off' end as "OnlineStatus"
,Computers.CurrentUser
, LogicalDisks.LogicalDeviceId AS "Drive Letter"
, ( (LogicalDisks.Size - LogicalDisks.FreeSpace) / 1024 / 1024 / 1024 ) AS "Used Space GB"
,(LogicalDisks.Size / 1024 / 1024 / 1024) as "DiskSize GB"
,(LogicalDisks.FreeSpace / 1024 / 1024 /1024) as "FreeSpace GB"
,(LogicalDisks.FreeSpace / 1024 / 1024) as "FreeSpace MB"
,(((LogicalDisks.FreeSpace / 1024 / 1024 / 1024 ) / (LogicalDisks.Size / 1024 / 1024 / 1024)) * 100) as "Percent available"
FROM
Computers
INNER JOIN
LogicalDisks USING (ComputerId)
where
/*filtering for the drive letter of C only*/
LogicalDisks.LogicalDeviceId="C:"

/*Filtering for where the available disk space is less then 2GB*/
and (logicalDisks.FreeSpace / 1024 / 1024 / 1024)<=2

1

Comments

2 comments
Date Votes
  • That's because those values are integers, so the result of division is also an integer (gets rounded down to 0). You have to convert them to floating-point numbers first, such as by multiplying them by 1.0.

    SELECT
    Computers.Name AS "Computer Name"
    , CASE WHEN Computers.IsOnline = 1 THEN "Online" ELSE 'Off' END AS "OnlineStatus"
    , Computers.CurrentUser
    , LogicalDisks.LogicalDeviceId AS "Drive Letter"
    , ((LogicalDisks.Size - LogicalDisks.FreeSpace) / 1024 / 1024 / 1024) AS "Used Space GB"
    , (LogicalDisks.Size / 1024 / 1024 / 1024) AS "DiskSize GB"
    , (LogicalDisks.FreeSpace / 1024 / 1024 / 1024) AS "FreeSpace GB"
    , (LogicalDisks.FreeSpace / 1024 / 1024) AS "FreeSpace MB"
    -- Multiply by 1 to cast the integer values into floating-point: https://dba.stackexchange.com/a/160587
    -- SQLite ROUND: https://www.sqlite.org/lang_corefunc.html#round
    , ROUND((((1.0 * LogicalDisks.FreeSpace) / (1.0 * LogicalDisks.Size)) * 100), 1) AS "Percent Available"
    FROM
    Computers
    INNER JOIN
    LogicalDisks USING (ComputerId)
    WHERE
    /*Filtering for the drive letter of C only*/
    LogicalDisks.LogicalDeviceId = "C:"
    AND
    /*Filtering for where the available disk space is less than 2GB*/
    (LogicalDisks.FreeSpace / 1024 / 1024 / 1024) <= 2
    2
  • Thanks Colby,

    That worked perfectly. As usual with all matters IT the error was found between the keyboard and the chair :) 

    Thank again and have a great weekend.

    1