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