MySQL：从聚合结果中检索分组行的总和

Question

假设我有每个国家用户访问次数的统计：

查询如下：

SELECT 
    countries_users.user_id,
    countries.name,
    count(countries_users.id) as count_visit
FROM countries_users
LEFT JOIN countries on countries.id = countries_users.user_id
WHERE countries_users.user_id IN (111, ...)
GROUP BY countries_user.user_id, countries.id

结果如下：

user_id | countries | count_visit
-------------------------------
111     | Norway    |   5
111     | Japan     |   2
...     | ...       |   ...

现在，通常情况下，我会在代码层面完成这个操作。然而，由于某种愚蠢的原因，我想在结果集中添加一个额外的列，该列是用户的总访问次数，不考虑国家。

所以，结果将变为：

user_id | countries | count_visit |  overall_visit
---------------------------------------------------
111     | Norway    |   5         |   7
111     | Japan     |   2         |   7
...     | ...       |   ...       |   ...

Answer 1

may you need a subquery

SELECT 
cu.user_id,
c.name AS countries,
count(cu.id) AS count_visit,
visitall.total_count_visit AS overall_visit
FROM countries_users cu
LEFT JOIN countries c ON c.id = cu.user_id
LEFT JOIN (
    SELECT user_id, SUM(count(id)) AS total_count_visit
    FROM countries_users
    WHERE user_id IN (111, ...) 
    GROUP BY user_id
) AS visitall ON cu.user_id = visitall.user_id
WHERE cu.user_id IN (111, ...)  
GROUP BY cu.user_id, c.id, visitall.total_count_visit