Sum Function with division

Question

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 converting to megabytes.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.