Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to insert some formula in mysql?

i want to calculating one row at table "counter". i try to make my table like:

name          black     yellow     white        qty_job      total
david           1        0          0            2             ?
andrew          0        1          1            4              ?

the formula to calculate is :

total = (nblack * 1) + (nyellow * 1) + (nwhite * 0.4) / qty_job
total = (1 * 1) + (0 * 1) + (0 * 0.4) / 2 = 0.5

how to insert this formula at mysql code? especially at SELECT method.

like image 282
klox Avatar asked Feb 26 '23 09:02

klox


2 Answers

You shouldn't / can't make a row with a certain formula in it. You should use this query to retrieve the total:

SELECT
    name,
    black,
    yellow,
    white,
    qty_job
    (SUM(black) + SUM(yellow) + SUM(white)*0.4) / qty_job AS total
FROM counter
GROUP BY name;
like image 182
Lekensteyn Avatar answered Mar 05 '23 16:03

Lekensteyn


Another alternative is to create a view :

CREATE VIEW test AS
SELECT id, (black * 1) + (yellow * 1) + (white * 0.4) / qty_job as total FROM counter;

The rest should be easy, you could do something like this :

select
 counter.id,
 black,
 yellow,
 white,
 test.total
from
 counter,
 test
where
  counter.id = test.id
like image 23
Hendra Jaya Avatar answered Mar 05 '23 15:03

Hendra Jaya