mutiemule mutiemule - 3 months ago 10
SQL Question

Calculate file sizes in sql

I have document file sizes as follows in a table:

Filesize
7013
14050
1300050
7775


The file sizes are in bytes by default.
I want to use sql to cast the file sizes into KBs, MBs or GBs accordingly.

Have been able to use the following sql to calculate the file size,
but i dont know how to group them into KBs, MBs, GBs.

SELECT CAST(ROUND(SUM(CAST(filesize AS Numeric(15,4))/1024),2) AS decimal(10,2))
FROM Attachments


My ultimate goal is to have:

6.848 KBs
13.720 KBs
1.240 MBs
7.593 KBs


I am using the conversion rate of 1KiloByte = 1024 bytes

Answer

You could try this

DECLARE @X NUMERIC(18,4) =  23

SELECT CASE WHEN @X <= 1024                         THEN   CONVERT(NVARCHAR, @X) + ' BYTES'
            WHEN @X >1024 AND @X <= 1048576         THEN   CONVERT(NVARCHAR, @X/1024) + ' KB'
            WHEN @X >1048576 AND @X <= 1073741824       THEN   CONVERT(NVARCHAR, @X/1048576) + ' MB'
            WHEN @X >1073741824 AND @X <= 109951162777  THEN   CONVERT(NVARCHAR, @X/1073741824) + ' GB'
            ELSE CONVERT(NVARCHAR, @X/109951162777) + ' TB'
       END

To use your table and field for example

    SELECT CASE WHEN yt.FileSize <= 1024                            THEN   CONVERT(NVARCHAR, yt.FileSize) + ' BYTES'
                    WHEN yt.FileSize >1024 AND yt.FileSize <= 1048576           THEN   CONVERT(NVARCHAR, yt.FileSize/1024) + ' KB'
                    WHEN yt.FileSize >1048576 AND yt.FileSize <= 1073741824     THEN   CONVERT(NVARCHAR, yt.FileSize/1048576) + ' MB'
                    WHEN yt.FileSize >1073741824 AND yt.FileSize <= 109951162777    THEN   CONVERT(NVARCHAR, yt.FileSize/1073741824) + ' GB'
                    ELSE CONVERT(NVARCHAR, yt.FileSize/109951162777) + ' TB'
               END
    FROM YourTable yt
Comments