CODEWITHSUNDEEP
sqloracle-databasecasting
Codash
Codash

Reputation: 99

Converting value into a Decimal

I have a percent column that saves values as a whole number. I need to calculate the percent of payments from a payment column by multiplying by the percent column. Currently my table looks like the following:

+----+---------+---------+
| ID | PAYMENT | PERCENT |   
+----+---------+---------+
|  1 |     500 |      15 |  
|  2 |      75 |       5 |   
|  3 |    1250 |      20 |   
+----+---------+---------+

I need the output to look as follows:

+----+---------+---------+-------------+
| ID | PAYMENT | PERCENT |  COMMISSION |
+----+---------+---------+-------------+
|  1 |     500 |     .15 |          75 |
|  2 |      75 |     .05 |        3.75 |
|  3 |    1250 |     .20 |         250 |
+----+---------+---------+-------------+

I have tried different variations of the below to convert the percent column into a decimal:

cast(percent as numeric (5,2))
cast(percent as decimal(5,2)) 
to_char(percent,'9999.99')

Upvotes: 0

Views: 44

Answers (1)

MT0
MT0

Reputation: 168806

Divide by 100.

SELECT id,
       payment,
       percent / 100 AS percent,
       payment * percent / 100 AS commission
FROM   table_name;

So for your data:

CREATE TABLE table_name ( id, payment, percent ) AS
SELECT 1,  500, 15 FROM DUAL UNION ALL
SELECT 2,   75,  5 FROM DUAL UNION ALL
SELECT 3, 1250, 20 FROM DUAL;

This would output:

ID | PAYMENT | PERCENT | COMMISSION
-: | ------: | ------: | ---------:
 1 |     500 |     .15 |         75
 2 |      75 |     .05 |       3.75
 3 |    1250 |      .2 |        250

db<>fiddle here

Is there another way [to divide by 100]?

Any of these would work:

SELECT id,
       percent / 100 AS opt1,
       percent * 0.01 AS opt2,
       TO_NUMBER( percent || 'e-2' ) AS opt3,
       percent * POWER( 10, -2 ) AS opt4,
       ROUND(POWER(10,LOG(10, percent)-2),2) AS opt5
FROM   table_name;

and outputs:

ID | OPT1 | OPT2 | OPT3 | OPT4 | OPT5
-: | ---: | ---: | ---: | ---: | ---:
 1 |  .15 |  .15 |  .15 |  .15 |  .15
 2 |  .05 |  .05 |  .05 |  .05 |  .05
 3 |   .2 |   .2 |   .2 |   .2 |   .2

But don't; just use the simplest method that you can.

db<>fiddle here

Upvotes: 1

Related Questions