MySQL: Retrieve sum of grouped rows from aggregate results

Question

Suppose I have statistics on the number of visits by users in each country:

The query is as follows:

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

The results are as follows:

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

Now, normally, I would do this at the code level. However, for some stupid reason I want to add an extra column to the result set which is the total number of visits by the user, regardless of country.

So, the result will become:

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