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 ... | ... | ... | ...
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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