CODEWITHSUNDEEP
sqlmathpostgresql
nwb
nwb

Reputation: 683

Postgres math expression calculcated for each row in table

Using PostgreSQL, supposing a table like the following:

 12184 |               4 |          83
 12183 |               3 |         171
 12176 |               6 |          95

How can I compute a math expression for each row in the table?

For example, to divide column 2 by column 3, such that the output would be:

 12184 |   0.04819277108
 12183 |   0.01754385965
 12176 |   0.06315789474

My instinct was to try:

SELECT col1, col2 / col3 FROM table_name;

But that return the ceiling (ie. rounded-down) integer part, I need the floating point value.

Upvotes: 22

Views: 38614

Answers (5)

Ankit Vishwakarma
Ankit Vishwakarma

Reputation: 1691

In PgSql the columns are typed. So if you want to operator on them; you need to cast the column.

suppose you have a column 'minutes' and you wanna add '+5' in every values of column 'mintues'

Because you are adding and integer value the minutes column must be a integer only then the addition can be performed.

hence incorrect way:

select *, minutes+5 from my table >> syntax error

select *, minutes::int + 5 from mytable >> give the output

Upvotes: 0

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340296

Typical cast trick needed because col2 and col3 are integers (so result is by default an integer)

select col1, col2/col3*1.0 from table

or

select col1, col2/col3::float from table

or (SQL Standard way)

select col1, col2/cast(col3 as float) from table

Upvotes: 34

Szymon Lipiński
Szymon Lipiński

Reputation: 28634

Try query like this:

SELECT col1, col2 / col3::float FROM table_name;

Upvotes: 0

Dmitry
Dmitry

Reputation: 3780

You can use arithmetic expressions in SELECT clause, like this:

SELECT col1 / col2 AS new_name
FROM t

Upvotes: 2

Paul Tomblin
Paul Tomblin

Reputation: 182802

select col1, col2/col3 from table;

Should work. Aren't col2 and col3 numeric?

Upvotes: 0

Related Questions