mysql - GROUP BY导致SUM()问题

我正在尝试运行销售报告查询。

我使用SUM()收到的购买总额不正确：看来问题可能出在我的GROUP BY上。

查询：

SELECT salesreport.canprevid,
  storelist.organization,
  SUM(quantity * unitprice * (100 - discount) / 100) AS total,
  storelist.external_manager,
  storelist.manager
  FROM storelist
  INNER JOIN salesreport
    ON storelist.store_id = salesreport.canprevid
  WHERE salesreport.date >= "2016-01-01"
    AND salesreport.date <= "2016-01-31"
  GROUP BY canprevid
  ORDER BY organization DESC;

如预期的那样，我能够拉：

|canprevid|organization|total (for the month of january 2016)| external manager| manager

但是，当我使用total总计SUM()时，total不等于该月（例如2016年1月）的真实总金额。

我不确定为什么某些记录会被删除，或者GROUP BY是否导致计算问题。

最佳答案

将storelist.organization添加到您的GROUP BY中：

SELECT salesreport.canprevid,
  storelist.organization,
  SUM(quantity * unitprice * (100 - discount) / 100) AS total,
  storelist.external_manager,
  storelist.manager
  FROM storelist
  INNER JOIN salesreport
    ON storelist.store_id = salesreport.canprevid
  WHERE salesreport.date >= "2016-01-01"
    AND salesreport.date <= "2016-01-31"
  GROUP BY salesreport.canprevid, storelist.organization
  ORDER BY storelist.organization DESC;

StoreList

mysql - GROUP BY导致SUM()问题