mysql查询:怎样才能只按cpm.ad_id分组，并拥有所有cc的总和？

Question

我有两个表- cpm(cc、ad_id和cpm)和clicks(cc和ad_id)。

当我运行时：

   SELECT cpm.ad_id, 
          COUNT(clicks.id) * cpm.cpm AS clicks_income     
     FROM cpm 
LEFT JOIN clicks on clicks.ad_id = cpm.ad_id and clicks.cc = cpm.cc
 GROUP BY cpm.ad_id, cpm.cc

我得到了：

ad_id | clicks_income
---------------------
1     | 271.00
1     | 2.60
2     | 238.00

现在我想要的结果是：

ad_id | clicks_income
---------------------
1     | 273.60
2     | 238.00

如何仅按cpm.ad_id进行分组，并具有所有cc的总和

当我删除cpm.cc时，我得到：

ad_id | clicks_income
---------------------
1     | 273.00
2     | 238.00

Answer 1

不要使用COUNT(whatever) * acolumn，而要使用SUM(AColumn)。

此外，LEFT JOIN在这里没有意义，因为不匹配只会将NULL加到和上，这不是很有帮助。

SELECT cpm.ad_id, SUM(cpm.cpm) AS clicks_income 
FROM cpm INNER JOIN clicks ON clicks.ad_id = cpm.ad_id AND clicks.cc = cpm.cc
GROUP BY cpm.ad_id

Answer 2

SELECT cpm.ad_id, COUNT(clicks.id)*cpm.cpm AS clicks_income 
FROM cpm LEFT JOIN clicks ON clicks.ad_id = cpm.ad_id AND clicks.cc = cpm.cc
GROUP BY cpm.ad_id