mysql 左连接使用sum结果不对_为什么我在连接两个表的MYSQL查询中得到不正确的SUM？...

感谢我的评论，我设法创建了一个产生正确结果的查询：

SELECT

COUNT( distinct licenses.licenseID ) AS total,

SUM( temp.mc_gross / temp.quantity ) AS gross

FROM

licenses

LEFT JOIN

( SELECT txn_id, mc_gross, quantity

FROM transactions

WHERE valid = 'VERIFIED-PAID'

) AS temp ON licenses.txn_id = temp.txn_id

GROUP BY

licenses.licensetypeID正确的数据：

total gross

===================

389 7780.000000

14 NULL

2 NULL

5 NULL

3 60.000000

1 NULL

1 NULL更新 - 改进的查询：

SELECT

COUNT( licenses.licenseID ) AS total,

SUM( transactions.mc_gross / transactions.quantity ) AS gross

FROM

licenses

LEFT JOIN transactions ON licenses.txn_id = transactions.txn_id AND transactions.valid = 'VERIFIED-PAID'

GROUP BY

licenses.licensetypeID