Division ( / ) not giving my answer in postgresql
Solution 1
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
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
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
This query will round result to next integer
select round(dev_cost ::decimal / sell_cost + 0.5)
Related videos on Youtube
zeewagon
Updated on May 23, 2020Comments
-
zeewagon almost 4 years
I have a table
software
and columns in it asdev_cost
,sell_cost
. Ifdev_cost
is 16000 andsell_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.
-
zeewagon over 8 yearsIf sell_cost is 7500, we need to sell 3 software to recover dev_cost i.e. > 16000. That is my question
-
Vivek S. over 8 yearstry
select ceil(16000::numeric/7500)
-
zeewagon over 8 yearsselect ceil(dev_cost::numeric/sell_cost) from software; --worked
-
-
Mike Sherrill 'Cat Recall' over 8 yearsWe're talking about money. Cast to decimal, not float.
-
Ilmari Karonen over 8 years@Mike: Good point, although unlikely to matter in the OP's case. (All they're asking for is integer division that rounds up; floats will do just fine as an intermediate type for that, at least unless the original values are bigints.) Still, fixed.
-
Ilmari Karonen over 8 yearsThis will give the wrong result if
dev_cost
happens to be an integer multiple ofsell_cost
: see example. -
zeewagon over 8 yearsThis query is ok. But if the table more values, say for example, dev_cost is 6000 and sell_cost is 400, the answer 15 would be correct. But your query gives 16 as the answer. What should we do? I am new to SQL, correct me if I am wrong.
-
Ilmari Karonen over 7 years@MRah: Glad to be of assistance. :) BTW, instead of commenting on answers that you find useful, you can vote them up by clicking the upwards-pointing triangle icon in the top left corner of the answer. This will reward the answerer with reputation points and lets them know that their answer is appreciated, and it also helps the best answers rise to the top of the page.
-
o.v over 3 yearsIs there any real need to use ::decimal for each operand?
-
J. B. Rainsberger about 2 yearsEither round() or floor(x + 0.5), but not round(x + 0.5)