Finding a % from two values in postgresql [duplicate]

Question

This question already has answers here:

Division of integers returns 0 (2 answers)

PostgreSQL math operations (3 answers)

PostgreSQL: Division returns zeros only (1 answer)

Closed 6 years ago.

I have a table in a database which holds a value for space used, and a value for space available. What I want to do is find the % of used / free space.

select
  datetime AS "time",
  storage.name as metric,
  (used_disk_size / (available_disk_size + used_disk_size))*100 as "Total disk space"
from
  usage
inner join storage on usage.storage_id = storage.storage_id;

When I run the above I get 0s in all columns.

sticky bit · Accepted Answer · 2019-04-23 10:01:33Z

0

Likely the data types are integers. And a division of integers stays an integer. Cast one or all of the operands to decimal.

...
(used_disk_size::decimal / (available_disk_size::decimal + used_disk_size::decimal)) * 100 ...

answered Apr 23, 2019 at 10:01

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

you only need to cast one of the columns, e.g. Using used_disk_size::decimal would be enough

sticky bit Over a year ago

Yes, that's right and why I wrote "one or all" (or actually an arbitrary number greater that or equal one would be even more correct). I just like to be verbose in such cases and therefore cast all of them.

steve Over a year ago

Thanks, that fixed the problem.

Collectives™ on Stack Overflow

Finding a % from two values in postgresql [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related