Re: Why Doesn't SQL This Expression Work?

On Mon, 22 Aug 2005, Lane Van Ingen wrote:

> Hi, am trying to do a simple computation on two views, but for some reason
> the current_util_in computation always returns zero. All fields being used
> are integer.
>
>   select a.if_id,
>     a.in_count,
>     a.time_incr,
>     b.speed,
>     ((a.time_incr * b.speed) / 8) as possible_bytes,
>     (a.in_count / ((a.time_incr * b.speed) / 8) * 100) AS current_util_in,
>     from if_history_view1 a, speed_history_view1 b
>     where a.if_id = b.if_id
>     and a.if_id = 2;
>
> The inner computation (a.time_incr * b.speed / 8) evaluated properly to
> 7675200.
> Add the "in_count divide operation", and the result is zero.

Integer division doesn't follow all the same rules as normal division
would.  In particular (a/b)*c is not the same as a*c/b.

Also, I think you may be expecting rounding rather than truncation (and in
the case of (a.time_incr*b.speed)/8 can that not be a multiple of 8, and
if so what should happen?)