From: | "Dara Olson" <dolson(at)glifwc(dot)org> |
---|---|
To: | "naptrel" <naptrel(at)yahoo(dot)co(dot)uk> |
Cc: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: sum divided by count ends in zero |
Date: | 2009-11-25 13:51:17 |
Message-ID: | D0666F34C95145368966E0CDE521A815@GISWKSTN |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Thank you so very much!
Dara
----- Original Message -----
From: naptrel
To: Dara Olson
Cc: pgsql-novice(at)postgresql(dot)org
Sent: Wednesday, November 25, 2009 2:59 AM
Subject: Re: [NOVICE] sum divided by count ends in zero
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rikard Bosnjakovic | 2009-11-25 19:20:04 | Column aliases for GROUP BY and HAVING |
Previous Message | naptrel | 2009-11-25 08:59:08 | Re: sum divided by count ends in zero |