mysql - SQL SUMPRODUCT有所不同

Table WIDGET
    Columns Country, Contract, Price

Table WEIGHT
    Columns Contract, Weight

我正在尝试执行SUMRPRODUCT(Contract, Weight)/SUM(Weight)操作，但对于Country的所有值。我试过的是根据Weighted average in T-SQL (like Excel's SUMPRODUCT)改编的，看起来像

SELECT
    Country
    SUM(widget.price * weight.weight) / SUM(weight.weight)
FROM
    Widget
        INNER JOIN
    Weight ON Widget.contract = Weight.contract
WHERE
    Weight.contract >= '2016-01-01'
    AND Weight.contract <= '2016-12-01'

问题在于，它只计算一个Country值，而不是所有值。如何获取DISTINCT(Country)列表以及旁边筛选的合同的SUMPRODUCT()/SUM()？
样本数据

+-----------+---------+------------+-------+
| widget_id | country | contract   | price |
+-----------+---------+------------+-------+
|         4 | CA      | 2016-01-01 | 16.00 |
|         5 | CA      | 2016-02-01 | 32.00 |
|         6 | CA      | 2016-03-01 | 64.00 |
|         1 | US      | 2016-01-01 | 32.00 |
|         2 | US      | 2016-02-01 | 64.00 |
|         3 | US      | 2016-03-01 | 96.00 |
+-----------+---------+------------+-------+

+-----------+------------+--------+
| weight_id | contract   | weight |
+-----------+------------+--------+
|         1 | 2016-01-01 |      1 |
|         2 | 2016-02-01 |      8 |
|         3 | 2016-03-01 |     64 |
+-----------+------------+--------+

期望输出

+---------+-----------+
| Country | Wtd Price |
+---------+-----------+
| CA      | 59.835616 |
| US      | 91.616438 |
+---------+-----------+

最佳答案

你应该像这样按国家分组

SELECT
    Country
    SUM(widget.price * weight.weight) / SUM(weight.weight)
FROM
    Widget
        INNER JOIN
    Weight ON Widget.contract = Weight.contract
WHERE
    Weight.contract >= '2016-01-01'
    AND Weight.contract <= '2016-12-01'
GROUP BY Country

关于mysql - SQL SUMPRODUCT有所不同，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31622179/

sumproduct

mysql - SQL SUMPRODUCT有所不同