CODEWITHSUNDEEP
sqlsql-server-2008
mutiemule
mutiemule

Reputation: 2539

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

Upvotes: 0

Views: 4173

Answers (1)

Nisha
Nisha

Reputation: 1439

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 <= 1099511627776 THEN CONVERT(NVARCHAR, @X/1073741824) + ' GB'
ELSE CONVERT(NVARCHAR, @X/1099511627776) + ' 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/1099511627776) + ' TB'
END
FROM YourTable yt

Upvotes: 2

Related Questions