当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - 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.

网友答案:

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
网友答案:

select S.id,S.name,S.score,S.item_id,I.[weight], (cast(S.score as float) * cast(I.[weight] as float))/ sum(cast([weight] as float)) as LastScore from scores S inner join items I on s.id = i.id group by S.id,S.name,S.score,S.item_id,I.[weight]

分享给朋友:
您可能感兴趣的文章:
随机阅读: