CODEWITHSUNDEEP
sqlpostgresql
rferrisx
rferrisx

Reputation: 1728

How do I successfully cast a division equation in a Select statement to a floating point or percentage?

Below I create some views in PostgreSQL 9.2. I need to cast the last column in the output below but no matter how I try (e.g. using numeric,real,float) I can't get a floating point or percentage result. Subtraction also does not work. Multiplication and Addition do work.

Create View Precincts AS Select precinctID,Count(precinctID) As count_prcnt
From voterdb GROUP BY precinctID ORDER BY count_prcnt DESC;

Create View Residences AS Select precinctID,Count(Distinct residenceaddress) As unique_resadd 
From voterdb group by precinctid ORDER BY unique_resadd DESC;

\d Precincts
precinctid  | character varying(80)

count_prcnt | bigint

\d Residences
precinctid    | character varying(80)

unique_resadd | bigint

Select Precincts.precinctid,Residences.unique_resadd,Precincts.count_prcnt, CAST(Residences.precinctID AS FLOAT) / CAST(Precincts.precinctID AS FLOAT) 
from Residences,Precincts Where Precincts.precinctID = Residences.precinctID;

245        |           857 |        1392 |        1

Select Precincts.precinctid,Residences.unique_resadd,Precincts.count_prcnt, (Precincts.precinctID::float8  /  Residences.precinctID::float8) AS PCT 
From Residences,Precincts Where Precincts.precinctID = Residences.precinctID ORDER BY Precincts.precinctid;

101        |           593 |         959 |   1

Upvotes: 0

Views: 1373

Answers (1)

Bradia
Bradia

Reputation: 857

In the query below the where condition impose the IDs to be equal so the result of division is always 1, result of subtraction is 0 and finally result of sum is id*2, none of which would be float. However the results will be shown as float like 1.0 and 0.0;

Select Precincts.precinctid,Residences.unique_resadd,Precincts.count_prcnt,(Precincts.precinctID::float8 / Residences.precinctID::float8) AS PCT 
From Residences,Precincts 
Where Precincts.precinctID = Residences.precinctID 
ORDER BY Precincts.precinctid;

The logic behind the query is not accurate would you please elaborate on what are you trying to calculate?

Upvotes: 1

Related Questions