Division ( / ) not giving my answer in postgresql

sql postgresql division modulo
zeewagon picture zeewagon · Dec 29, 2015 · Viewed 73.5k times · Source

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.

Answer

Ilmari Karonen picture Ilmari Karonen · Dec 29, 2015

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.)

Related questions

Division of integers returns 0

I feel like I'm missing something obvious. I am trying to test out the distribution of random(). Here is the table: create table test ( id int, random_float float, random_int int ); Here is what I want to do: truncate …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More