Reputation: 99
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
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