Skip site navigation (1) Skip section navigation (2)

Re: sum divided by count ends in zero

From: naptrel <naptrel(at)yahoo(dot)co(dot)uk>
To: Dara Olson <dolson(at)glifwc(dot)org>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: sum divided by count ends in zero
Date: 2009-11-25 08:59:08
Message-ID: FDA6282B-E532-455F-8492-45A889F8D2F3@yahoo.co.uk (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Dara,

I suspect that the problem is that the division is acting on two integers, and doing integer division.

If you have two integers, a=3 and b=10, then:
a+b = 13
a*b = 30
b-a = 7

but:

b/a = 3   because 3 will fit 3 times into 10, with 1 remaining (i.e. b % a = 1)
a/b = 0   because the result is also an integer, and 10 will not go into 3 (note: a %b = 3)

You need to 'cast' the variables a and b to floating point values so that the division produces a floating point result

E.g.:

a::float / b::float = 0.3

Nathaniel


On 24 Nov 2009, at 22:32, Dara Olson wrote:

> Greetings.
>  
> I am having a problem dividing a sum by a count and end up with zero, however I am able to multiply, subtract and add. 
>  
> Here are samples of the two views:
> View that groups individuals into specific groups by numbers:
> SELECT
> SUM(CASE when indiv_total_lbs <= 50 then 1 else 0 end) as "0_50",
> SUM(CASE when indiv_total_lbs <= 100 AND indiv_total_lbs > 50 then 1 else 0 end) as "51_100", etc.
> FROM wr_harvest.sh_sum_by_harvestor
>  
> View that gathers overall stats:
> SELECT
> AVG (indiv_total_trips) AS ave_no_trips, etc.
> COUNT(DISTINCT x) AS no_harvestors, etc.
> FROM wr_harvest.sh_sum_by_harvestor
>  
> I want to find the percent of individuals in each catagory from the first view.
> SELECT  
> a."0_50"/b.no_harvestors *100 AS "0_50percent",
> a."51_100"/b.no_harvestors *100 AS  "51_100percent", etc
> FROM wr_harvest.sh_distribution_stats_no as a, wr_harvest.sh_harvestor_stats as b
>  
> I am able to add, subtract and mulitply the values with the correct outcomes, but every time I try to divide the two numbers I end in zero. 
> What am I doing wrong?  Any help would be greatly appreciated.
>  
> Miigwech in advance!
> Dara
>  

In response to

Responses

pgsql-novice by date

Next:From: Dara OlsonDate: 2009-11-25 13:51:17
Subject: Re: sum divided by count ends in zero
Previous:From: Syan TanDate: 2009-11-25 03:47:13
Subject: Re: Postgres blob question - insert from basic.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group