Rikad Fauzi  Alawi Rikad Fauzi Alawi - 3 months ago 20
MySQL Question

Ask SQL for multiplication two columns and then division with sum of some rows

I have tables

scores

id name score item_id
----------------------
1 rikad 90 1
2 rikad 80 2
3 rikad 70 3
4 reza 80 1
5 reza 80 2
6 reza 100 3


items

id weight
----------
1 0.5
2 0.2
3 0.3


I want the output with new column call last_score ( (score x weight ) / "0.5 + 0.2 + 0.3 (sum of all weight that have same name)" )

id name score item_id weight last_score
----------------------------------------
1 rikad 90 1 0.5 last_score
2 rikad 80 2 0.2 last_score
3 rikad 70 3 0.3 last_score
4 reza 80 1 0.5 last_score
5 reza 80 2 0.2 last_score
6 reza 100 3 0.3 last_score


I have tried this SQL:

SELECT
scores.id, scores.name,
items.id, items.weight,
scores.score * items.weight AS 'last_score'
FROM
scores
JOIN
items ON items.id = scores.item_id


I need to division the last score with the sum of all weight that have same name. But I have no idea to sum the weight.

Answer

You can use a subquery to get the sum of weights by name and join this back on the main tables using the name field:

SELECT scores.id, scores.name,items.id,items.weight, scores.score * items.weight/t.sum_weight AS 'last_score'
from scores
INNER JOIN items ON items.id = scores.item_id
INNER JOIN (SELECT scores.name, sum(items.weight) sum_weight
            FROM scores INNER JOIN items ON items.id = scores.item_id
            GROUP BY scores.name) t ON scores.name=t.name