Creating a column being the multiple of others

Luis Fernando Zanuncio 0
Viewed 30

I need some help. I have 2 colluns from mysql query result: 1 with text, and another with decimal values. Like that:

select desc, value from table a

|5,50 %     |   2984.59     |
|Subs       |   10951.70    |
|Isent      |   3973.17     |
|13,30 %    |   560.26      |

From the rows that have the %, I want to multiply the values and create a third result column, rounding up to two decimal places. See above

2984,59 * 0,055 = 164,15245
560,26 * 0,133 = 74,514

I need make the sql query that show something like above.

+-------+-----------+-----------+
|5,50 % | 2984,59   | 164,16    |
|Subs   | 10951,70  | 0 or NULL |
|Isent  | 3973,17   | 0 or NULL |
|13,30% | 560,26    | 74,52     |
+-------+-----------+-----------+

How i can do it?

Thanks so much for help

mysql sql
1 Answers

It would be better to have floaring numbers in the first place, converting costs time

You have commas in your procentage, but mysql needs dots there

If value isn't always a number, you can use the mysql way to add a 0 0 to it, that remioves all non numerical characters

SELECT `desc`, `value`,  (REPLACE(`desc`,',','.') + 0)   *  `value` / 100 FROM val
desc value (REPLACE(`desc`,',','.') + 0) * `value` / 100
5,50 % 2985 164.175
Subs 10952 0
Isent 3973 0
13,30 % 560 74.48

fiddle

SELECT `desc`, `value`,  CEIL((REPLACE(`desc`,',','.') + 0)   *  `value`) / 100 FROM val
desc value CEIL((REPLACE(`desc`,',','.') + 0) * `value`) / 100
5,50 % 2985 164.18
Subs 10952 0
Isent 3973 0
13,30 % 560 74.48

fiddle

nbk0
Related