Division ( / ) not giving my answer in postgresql
SqlPostgresqlDivisionModuloSql Problem Overview
I have a table software
and columns in it as dev_cost
, sell_cost
. If dev_cost
is 16000 and sell_cost
is 7500.
How do I find the quantity of software to be sold in order to recover the dev_cost
?
I have queried as below:
select dev_cost / sell_cost from software ;
It is returning 2 as the answer. But we need to get 3, right?
What would be the query for that? Thanks in advance.
Sql Solutions
Solution 1 - Sql
Your columns have integer types, and integer division truncates the result towards zero. To get an accurate result, you'll need to cast at least one of the values to float or decimal:
select cast(dev_cost as decimal) / sell_cost from software ;
or just:
select dev_cost::decimal / sell_cost from software ;
You can then round the result up to the nearest integer using the ceil()
function:
select ceil(dev_cost::decimal / sell_cost) from software ;
(See demo on SQLFiddle.)
Solution 2 - Sql
You can cast integer type to numeric
and use ceil()
function to get the desired output
> The PostgreSQL ceil function returns the smallest integer value that > is greater than or equal to a number.
SELECT 16000::NUMERIC / 7500 col
,ceil(16000::NUMERIC / 7500)
Result:
col ceil
------------------ ----
2.1333333333333333 3
So your query should be
select ceil(dev_cost::numeric/sell_cost)
from software
Solution 3 - Sql
You can also cast your variable to the desired type, then apply division:
SELECT (dev_cost::numeric/sell_cost::numeric);
You can round your value , and specify the number of digits after point:
SELECT TRUNC((dev_cost::numeric/sell_cost::numeric),2);
Solution 4 - Sql
This query will round result to next integer
select round(dev_cost ::decimal / sell_cost + 0.5)