Displaying the value with the most occurrences

Question

I have two related tables:

(1) people contains names and image files.

(2) cities contains cities they have visited.

people

id name     image
1  John     NULL
2  Carrie   001.jpg
3  Desmond  002.jpg
4  Harry    003.jpg
5  Paul     NULL

cities

id  city      people_id   year_visited
1   Chicago   1           2000
2   Chicago   4           2000
3   Chicago   5           2001
4   Paris     1           2000
5   Paris     2           2002
6   Chicago   4           2002
7   Chicago   1           2001
8   London    1           2004
9   Sydney    5           2001
10  Sydney    1           2002
11  Rio       5           2002
12  London    5           2004
13  Sydney    5           2003
14  Sydney    5           2005

I would like to identify all people without an image, and the city they have visited the most. So the results I am looking for is:

name  most_visited_city number_of_visits
John  Chicago           2
Paul  Sydney            3

I can group_concat the cities they have visited, but not drill down to the single city they visited the most.

All help gratefully appreciated.

score 0 · Answer 1 · answered Aug 19 '16 at 02:33

The following gets people, cities, and the count:

select p.id, c.city, count(*) as cnt
from people p join
     cities c
     on p.id = c.people_id
where p.image is null
group by p.id, c.city;

Getting information about the most visited is tricky in MySQL. Here is one method that works if the data is not too large:

select id,
        substring_index(group_concat(city order by cnt desc separator '|'), '|', 1) as most_visited_city,
        max(cnt) as number_of_times_visited 
from (select p.id, c.city, count(*) as cnt
      from people p join
           cities c
           on p.id = c.people_id
      where p.image is null
      group by p.id, c.city
     ) pc
group by id;

Thank you. I actually used your initial query (it was the additional grouping by c.city that I'd missed) and wrapped in inside an outer select statement: `select * from ( select p.id, c.city, count(*) as cnt from people p join cities c on p.id = c.people_id where p.image is null group by p.id, c.city order by cnt desc ) t1 group by t1.id` Those last two lines leaves me with the person ID and the city they visited most. Thanks again! — huey, Aug 26 '16 at 01:43

score 0 · Answer 2 · edited May 23 '17 at 12:15

This query should return the most visited city for each people_id in cities.

SELECT t1.people_id, t2.city, t2.visits
FROM (
    SELECT people_id, MAX(visits) AS max_visits
    FROM (
        SELECT people_id, city, COUNT(*) AS visits
        FROM cities
        GROUP BY people_id, city) x
    GROUP BY people_id) AS t1
JOIN (
    SELECT people_id, city, COUNT(*) AS visits
    FROM cities
    GROUP BY people_id, city) AS t2
ON t1.people_id = t2.people_id AND t1.max_visits = t2.visits

The general structure is based on an answer in SQL Select only rows with Max Value on a Column, but instead of getting the max value of a column in the table, it's using the max value in the subquery that counts visits per city. Unfortunately, it results in an ugly query because you have to repeat that subquery, since MySQL doesn't have CTEs.

Then you can join it with people to get the person's name and filter out the ones with an image.

SELECT p.name, t2.city, t2.visits
FROM (
    SELECT people_id, MAX(visits) AS max_visits
    FROM (
        SELECT people_id, city, COUNT(*) AS visits
        GROUP BY people_id, city) x
    GROUP BY people_id) AS t1
JOIN (
    SELECT people_id, city, COUNT(*) AS visits
    GROUP BY people_id, city) AS t2
ON t1.people_id = t2.people_id AND t1.max_visits = t2.max_visits
JOIN people AS p ON p.id = t1.people_id
WHERE p.image IS NULL

DEMO

Thanks @Barmar, I went with a different solution, but this looks just as effective. — huey, Aug 26 '16 at 01:43

Displaying the value with the most occurrences

2 Answers2