CODEWITHSUNDEEP
sqlpostgresql
jaroApp
jaroApp

Reputation: 899

PostgreSQL math operations

I have simple query

select round((bca_total_lean/(bca_total_meta + bca_total_fat))*100,0) as lean_mass_percent from x where...

as result, I see 0. for bca_total_lean/bca_total_fat result is ok. By bca_total_meta is 0.

All fields are integers and all results are greater than 0. The same operation in this data in PHP returns 83. I also tried without a round function. The same result. Separated data return by BD

Any hints where is an error?

Upvotes: 0

Views: 847

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Postgres does integer division. So, I would express this as:

select round(bca_total_lean * 100.0 / (bca_total_meta + bca_total_fat), 0) as lean_mass_percent
from x
where . . .

Upvotes: 2

Sookie Singh
Sookie Singh

Reputation: 1623

Integer division truncates fractional digits. Your expression returns a ratio between 0 and 1, which is always truncated to 0.

To get "percentage", first multiply by 100.

select round((bca_total_lean*100)/(bca_total_meta + bca_total_fat),0) as lean_mass_percent from x where...

Upvotes: 3

Oto Shavadze
Oto Shavadze

Reputation: 42793

Try:

select round((bca_total_lean::numeric/(bca_total_meta::numeric + bca_total_fat::numeric))*100,0) as lean_mass_percent
from x where...

Upvotes: 0

Related Questions