convert file size in bytes to GB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

convert file size in bytes to GB

Author

Topic

megala
Starting Member

23 Posts

Posted - 2009-03-24 : 03:12:42

Hi,

I have problem in converting a filesize field that stores file sizes in bytes. I need to sum up the filesize of users and display the sum.

How can i convert and display the sum of 4387672143 as 4.38 GB

Please help as im stuck.

The sql i have now is

SELECT top 15 a.domainname,a.transferdomainname, sum(cast(filesize/1024*1024 as bigint))as totalsize FROM tbl_mediafiles m,tbl_useraccounts a where a.accountid=m.createdby GROUP BY a.domainname,a.transferdomainname order by totalsize desc

And my results for filesize is in this format

4384495616
2149193728

Can this be achieved in one sql using cast or i need to get this value and do a convert in asp.net.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-03-24 : 03:56:12

sum(cast(filesize AS Numeric(15,4)/1024/1024/1024)as totalsize

Without brackets, your query was first dividing by 1024 then multiplying by 1024

--
Gail Shaw
SQL Server MVP

megala
Starting Member

23 Posts

Posted - 2009-03-24 : 04:56:28

Hi Gail,

Thanks for the reply.
Before i saw your post i tried something like this
"sum((cast(filesize as decimal))/1073741824)as totalsize" and my result was like 4.08633808563

I tried your query and there was a bracket missing. SO i changed it to
"sum(cast(filesize AS Numeric(15,4))/1024/1024/1024)as totalsize"

now im getting the result as 4.0863381167859435519

sorry to ask again, how can i round it up to 2 decimal point as in 4.08. i'm trying to cast it again, but im getting error.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-24 : 09:32:32

cast(round(sum(cast(filesize AS Numeric(15,4))/1024/1024/1024),2) as decimal(10,2))as totalsize

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-24 : 15:11:01

CAST(FileSize / 1073741824.0E AS DECIMAL(10, 2))

E 12°55'05.63"
N 56°04'39.26"

megala
Starting Member

23 Posts

Posted - 2009-04-01 : 22:56:16