MySQL 左连接中的查询总和

Question

下面的查询在选择中使用 SUM 并连接两个表。 SUM 的结果包括所有期间的金额。我怀疑这是由于 SUM 的放置位置所致。我认为帖子here包含与我遇到的相同问题。我尝试按照给出的解决方案进行操作，但似乎无法使其正常工作。

查询:

SELECT
  Mo911ZipLookup.X_STATE AS StateAbbr,
  Mo911ZipLookup.X_CITY AS City,
  Mo911ZipLookup.X_COUNTY AS County,
  SUM(SourceDataTCX.E911Amount) AS E911Amount,
  SourceDataTCX.period AS period
FROM (SourceDataTCX
  LEFT JOIN Mo911ZipLookup
    ON ((SourceDataTCX.ZipCode = Mo911ZipLookup.X_ZIPCODE)))
WHERE (Mo911ZipLookup.X_STATE = 'MO' AND SourceDataTCX.period = '2019-02-01')
GROUP BY Mo911ZipLookup.X_STATE,
         Mo911ZipLookup.X_CITY,
         Mo911ZipLookup.X_COUNTY,
         SourceDataTCX.period
ORDER BY Mo911ZipLookup.X_STATE, Mo911ZipLookup.X_COUNTY, Mo911ZipLookup.X_CITY

查询应仅返回 2019-02-01 期间的金额，但它包括所有期间的金额。我认为 SUM 应该移到连接中。有人可以帮我吗？

编辑该查询产生如下结果:

StateAbbr   City    County  E911Amount  period  
MO  BALLWIN SAINT LOUIS 614.80  2019-02-01
MO  ELLISVILLE  SAINT LOUIS 614.80  2019-02-01
MO  MANCHESTER  SAINT LOUIS 614.80  2019-02-01
MO  TWIN OAKS   SAINT LOUIS 614.80  2019-02-01
MO  WILDWOOD    SAINT LOUIS 614.80  2019-02-01
MO  WINCHESTER  SAINT LOUIS 614.80  2019-02-01

每个城市的总金额显示为所有城市的总金额。我认为 SUM 在 GROUP BY 之前应用。

Answer 1

将过滤器 Mo911ZipLookup.X_STATE = 'MO' 移至 ON 子句:

 SELECT Mo911ZipLookup.X_STATE AS StateAbbr,
   Mo911ZipLookup.X_CITY AS City,
   Mo911ZipLookup.X_COUNTY AS `County,,
   z.E911Amount,
   z.period AS period FROM
(
 SELECT SUM(E911Amount`) AS E911Amount,period AS period, ZipCode
 FROM SourceDataTCX
 WHERE period = '2019-02-01'
GROUP BY period, ZipCode      
) z
LEFT JOIN Mo911ZipLookup ON  z.ZipCode = Mo911ZipLookup.X_ZIPCODE
                      AND Mo911ZipLookup.X_STATE = 'MO' 
ORDER BY Mo911ZipLookup.X_STATE, Mo911ZipLookup.X_COUNTY, Mo911ZipLookup.X_CITY