将值相乘的Mysql查询返回double

Question

我试图从一张表中获取按周计算的单位销售额，然后乘以另一张表中的定价，然后按产品获得结果。我用了this Stack Overflow post .结果恰好是应有的两倍。

查询看起来像:

SELECT product, week, SUM(units * price) AS revenue
FROM weekly_sales
JOIN pricing ON
    weekly_sales.product = pricing.product
GROUP BY product, week;

我尝试了一些方法，例如使用 DISTINCT。最初是在进行第二次连接以获取目录信息，但删除了它以确保这不是问题所在。我还使用 WHERE week = current.week 来查看是否可以让它工作一周，但同样的问题。谢谢!

weekly_sales 看起来像:

product    | units    | week
B001       |     5    | 41
B001       |     2    | 42
B002       |     3    | 41
B002       |     8    | 42

定价表如下所示:

product | current_price | week
B001    |     6.99      | 41
B002    |     8.99      | 41

结果如下:

product | week | revenue
B001    |  42  | 69.9
B001    |  41  | 27.96
B002    |  42  | 53.94

Answer 1

我认为你应该在加入中再添加一个条件如下:

SELECT product, week, (units * price) AS revenue
FROM weekly_sales
JOIN pricing ON
    weekly_sales.product = pricing.product
and weekly_sales.week = pricing.week
GROUP BY 1, 2;

Click here for DEMO

希望对您有所帮助!