Обсуждение: Why Doesn't SQL This Expression Work?
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. The result expected is a percentage, and should compute to 7.68 (8 as an integer), when multiplied by 100. What is wrong here? Here is the result: Row if_id in_count time_incr speed possible_bytes current_util_in 1 2 589824 240 255840 7675200 0
I believe the problem is that the expression is being eavluated as an integer, so it's rounded down before it's multiplied by 100; A simple cast to float4 should help: test=> select (589824 / ((240 * 255840) / 8) * 100);?column? ---------- 0 (1 row) test=> select (589824 / ((240 * 255840)::float4 / 8) * 100); ?column? ------------------7.68480300187617 (1 row) Dmitri > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Lane Van Ingen > Sent: Monday, August 22, 2005 3:51 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Why Doesn't SQL This Expression Work? > > > 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. > > The result expected is a percentage, and should compute to > 7.68 (8 as an integer), when multiplied by 100. What is wrong > here? Here is the result: > Row if_id in_count time_incr speed possible_bytes > current_util_in > 1 2 589824 240 255840 7675200 0 > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > The information transmitted is intended only for the person or entity to which it is addressed and may contain confidentialand/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any actionin reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you receivedthis in error, please contact the sender and delete the material from any computer
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?)
Thanks again, Dmitri. I put a round() function around it, and got exactly what I was looking for. Tried a cast earlier, but put it in the wrong place! :-( Just noted that the manual (version 8.0, section 9.3) warns about integer arithmetic and truncation of results, but fortunately there is a way around it! Thanks again .... -----Original Message----- From: Dmitri Bichko [mailto:dbichko@aveopharma.com] Sent: Monday, August 22, 2005 4:42 PM To: Lane Van Ingen; pgsql-sql@postgresql.org Subject: RE: [SQL] Why Doesn't SQL This Expression Work? I believe the problem is that the expression is being eavluated as an integer, so it's rounded down before it's multiplied by 100; A simple cast to float4 should help: test=> select (589824 / ((240 * 255840) / 8) * 100);?column? ---------- 0 (1 row) test=> select (589824 / ((240 * 255840)::float4 / 8) * 100); ?column? ------------------7.68480300187617 (1 row) Dmitri > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Lane Van Ingen > Sent: Monday, August 22, 2005 3:51 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Why Doesn't SQL This Expression Work? > > > 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. > > The result expected is a percentage, and should compute to > 7.68 (8 as an integer), when multiplied by 100. What is wrong > here? Here is the result: > Row if_id in_count time_incr speed possible_bytes > current_util_in > 1 2 589824 240 255840 7675200 0 > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer