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.
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;
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With