Calculated report column displaying 0 zero

Comments

2 comments

  • Colby Bouma

    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
    Comment actions Permalink
  • Mcgregor Chris

    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
    Comment actions Permalink

Please sign in to leave a comment.