MySQL Math - calculate value from another column

Question

I need to create a view for with resulting math function multiply. Is this possible?

Table:

mysql> show create table devices \G
Create Table: CREATE TABLE `devices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `d_type` varchar(255) NOT NULL,
  `multiplier` char(255) NOT NULL DEFAULT '',
  `value` decimal(10,3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Sample rows:

mysql> SELECT devices.id, devices.d_type, devices.multiplier, devices.`value`  FROM devices;   
+----+--------------------------+------------+--------+
| id | d_type                   | multiplier | value  |
+----+--------------------------+------------+--------+
|  1 | Cisco Call Manager       | /          |  4.000 |
|  2 | Generic Router/Switch    | *          |  0.350 |

I need to somehow calculate using the multiplier to provide a result for various math. Example (not working of course):

SELECT devices.id, devices.d_type, devices.multiplier, devices.`value`, (SELECT 1 $multiplier devices.`value`) as EPS FROM devices;

The result in this case should be:

+----+--------------------------+------------+--------+--------+
| id | d_type                   | multiplier | value  |   EPS  |
+----+--------------------------+------------+--------+--------+
|  1 | Cisco Call Manager       | /          |  4.000 |  0.25  |
|  2 | Generic Router/Switch    | *          |  0.350 |  0.35  |

we need to derive the EPS column by dividing or multiplying 1 by the value column. Is there a way to use that multiplier as a determination of whether to divide or multiply?

(note that I used 1 as an example, this could be any incoming integer from user input)

Ajay Kadyan · Accepted Answer · 2012-08-16 05:22:08Z

1

You can use this query

SELECT devices.id, devices.d_type, devices.multiplier, devices.`value`, IF(multiplier = '/', 1/value, 1*value) as EPS FROM devices;

it works for you.

edited Aug 16, 2012 at 5:22

answered Aug 16, 2012 at 4:29

Ajay Kadyan

1,0893 gold badges13 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Clayton Dukes Over a year ago

Thanks!For the sake of completeness in case someone needs this later on, there was a syntax error. Here's the correct command:

SELECT devices.id, devices.d_type, devices.multiplier, devices.value, (IF(multiplier = '/', 1/value, 1*value)) AS EPS FROM devices;

CyberDem0n · Accepted Answer · 2012-08-16 04:25:58Z

0

IF(multiplier = '/', 1/value, value) AS EPS

answered Aug 16, 2012 at 4:25

CyberDem0n

15.1k1 gold badge37 silver badges24 bronze badges

Collectives™ on Stack Overflow

MySQL Math - calculate value from another column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related