Question 222729 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: Get the total of aggregated total rows of grouped result

Suppose I have a user’s count of visit for each countries:

The query goes:

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 result would be:

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

Now, normally, I would’ve done this on the code-level, However, for some stupid reason , I would like to append additional column to the result set, and that column is the total count_visit of the user regardless of the country.

So, instead of the result above, it will now be:

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

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    Test this:

    SELECT 
    countries_users.user_id,
    COALESCE(countries.name, 'Overall') country_name,
    count(countries_users.id) as count_visit
FROM countries_users
LEFT JOIN countries on countries.id = countries_users.user_id  
                    -- ?? maybe countries_users.country_id
WHERE countries_users.user_id IN ( {user_ids list} )
GROUP BY countries_user.user_id, countries.id WITH ROLLUP
HAVING NOT GROUPING(user_id)
ORDER BY user_id, country_name = 'Overall'

    You’d obtain something like

    user_id country_name count_visit
    111 Norway 5
    111 Japan 2
    111 Overall 7
    112 Germany 1
    112 Swedish 2
    112 Overall 3
    113 Taiwan 2
    … … …
    Login or Signup to reply.
  3. 0

    Use SUM OVER to add up the user’s visit counts.

    SELECT 
  cu.user_id,
  c.name,
  COUNT(c.id) AS count_visit,
  SUM(COUNT(c.id)) OVER (PARTITION BY cu.user_id) AS overall_visit
FROM countries_users cu
JOIN countries c on c.id = cu.country_id
WHERE cu.user_id IN (111, ...)
GROUP BY cu.user_id, cu.country_id
ORDER BY cu.user_id, cu.country_id;

    I’ve amended the join condition – you’ll want to join countries on the country ID not the user ID.

    And I turned the join into an inner join, because why would a countries_users row without a country even exist? If you do want users without any visits, then use the users table:

    SELECT
  u.user_id,
  c.name,
  COUNT(c.id) AS count_visit,
  SUM(COUNT(c.id)) OVER (PARTITION BY u.id) AS overall_visit
FROM users u
LEFT JOIN countries_users cu ON cu.user_id = u.id
LEFT JOIN countries c on c.id = cu.country_id
WHERE u.user_id IN (111, ...)
GROUP BY u.user_id, c.country_id
ORDER BY u.user_id, c.country_id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search