mysql - SQl - 计算每个人的平均金额

Question

我有两张 table

class
| id      | area       | students  |
|       1 |      area1 |     2     |
|       2 |      area1 |     28    |
|       3 |      area1 |     22    |
|       4 |      area2 |     4     |

deliveries
| id      | kg         | classid   |
|       1 |      120   |     1     |
|       2 |      80    |     1     |
|       3 |      20    |     1     |
|       4 |      200   |     2     |
|       5 |      150   |     3     |
|       6 |      14    |     2     |

我需要总结每个地区每个学生交付的平均公斤数。

对于 area1，应该等于 (120+80+20+200+150+14)/(2+28+22) = 11.23但我无法弄清楚如何编写该查询。我想我必须先使用某种子查询对 area1 (52) 中的学生求和，然后再对交付的公斤数求和并除以学生？

Answer 1

这有点棘手，因为学生应该与类(class)分开计算:

select c.area, sum(d.kg) / max(area_students) as avg_kg_per_student
from class c join
     deliveries d
     on d.classid = c.id join
     (select c2.area, sum(students) as area_students
      from class c2
      group by c2.area
     ) c2
     on c2.area = c.area
group by c.area;