Skip site navigation (1)
Skip section navigation (2)
## Re: sum divided by count ends in zero

### In response to

### Responses

### pgsql-novice by date

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 >

- sum divided by count ends in zero at 2009-11-24 22:32:10 from Dara Olson

- Re: sum divided by count ends in zero at 2009-11-25 13:51:17 from Dara Olson

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