Menu
I am storing a bigint value (for file sizes) in a table. I need to group on one column and for the filesizes (which are in bytes). I would like to have a column showing them by GB. This would mean Sum(FileSize/1024/1024/1024) which is not showing any decimal places. My research seems to indicate this may be due to truncation rather than rounding.
I have tried many options of cast and convert, but cannot seem to find any information about how to sum and divide a bigint, and maintain the decimals. If I take the sum and divide it in Excel, I get the decimals, which tells me there has to be a way to do this in SQL.
Any help is appreciated.
513
BIGINT is to store large integers from negative 263 through positive 263 – 1. The storage size is eight bytes. BIGINT is intended for special cases where INTEGER range is"not sufficient. DECIMAL Valid values are in the range from negative 1038 +1 through positive 1038 – 1.
To store numbers that have fixed precision and scale, you use the DECIMAL data type. In this syntax: p is the precision which is the maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision has a range from 1 to 38.
Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2. In SQL Server, the default maximum precision of numeric and decimal data types is 38.
Sum(CAST(FileSize AS FLOAT)/1024/1024/1024)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
