MySQL:两个不同分组的计数和总计

Question

我正在努力处理以下 SQL:我有一个表，其中包含网站的 site_id 和引荐域。我试图简单地按引荐来源网址和站点计算出现次数，然后计算一个站点的所有引荐来源网址的总数。

在我下面的结果中，计数和站点总数不正确。计数应该是一半，例如对于站点 ID 1，谷歌和亚马逊的计数应为 10，而站点 ID 2 的总数应为 18。知道我可能做错了什么吗？

SELECT site_id, I.referrer_domain AS referrer_domain, COUNT(*) AS items, t.site_total
  FROM qVisitor_Tracking_1 as I, 
      (SELECT COUNT(1) AS site_total
         FROM `qVisitor_Tracking_1` 
        WHERE `referrer_domain` != '' group by site_id) AS T
 WHERE `referrer_domain` != '' 
 GROUP BY `site_id`, referrer_domain


site_id   referrer  count   site total
1         amazon    20      24
1         google    20      24
2         amazon    12      24
2         google    18      24

Answer 1

如果我理解您的要求，您似乎缺少将子查询连接到主表的 ON 子句。

SELECT I.site_id, 
        I.referrer_domain AS referrer_domain, 
        COUNT(*) AS items, 
        T.site_total 
FROM qVisitor_Tracking_1 as I
INNER JOIN 
(
    SELECT site_id, 
            COUNT(1) AS site_total 
    FROM qVisitor_Tracking_1 
    WHERE referrer_domain != '' 
    GROUP BY site_id
) AS T 
ON I.site_id = T.site_id
WHERE referrer_domain != '' 
GROUP BY I.site_id, referrer_domain, T.site_total