开发者

Sum Function with division

I am trying to figure out which is the correct format when using the sum function with division:

Which one is correct?

sum(filesize/1024)

or

sum(filesize)/1024

The filesize column is a measurement in kilobytes which I am 开发者_StackOverflow社区converting to megabytes.


They mean different things. sum(filesize/1024) reduces the file size to megabytes, and then sums megabytes. For each file, this "forgets" the size in excess of the megabytes boundary, so it will underreport the total file size.

sum(filesize)/1024 will report the correct size in megabytes-- rounded down.


Personally I'd go with option #2 (assuming the number's aren't so large you'll have overflow issues to deal with). This way the numbers stay as exact as possible until they're given to the user.


I'm pretty sure the first one is correct - sum(filesize/1024), but you'll want to make sure that filesize is never 0, or your query will error.

Use decode on oracle or if on SQL server to do that.


Logically, both should give the same result. However, sum(filesize)/1024 will probably be faster. If you go with the other option, you're performing the divide operation on each result, instead of just doing it once.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜