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

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
``````
Source (Stackoverflow)