SQL query to count most "popular" value?

Question

These are my tables:

Table: People

id---name
1----John
2----Mike
3----George

Table: Visits

id---name---visitor
1----Paris--1
2----Paris--1
3----Paris--1
4----London-1
5----London-1
6----Tokyo--1

Now from those tables we can read that a person named John (id 1) has visited Paris three times, London twice and Tokyo once. Now my question is how could I translate this into SQL and select the name of the most popular place John has visited (result being Paris)?

Answer 1

Take a look at aggregate functions: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

SELECT name, count(name) AS visits 
FROM visits
WHERE visitor = 1
GROUP BY name
ORDER BY visits DESC;

This will get you the number of visits for each specific location for that visitor and order the results with the most popular at the top.

If you want only the most popular, then you can limit the results:

SELECT name, count(name) AS visits
FROM visits
WHERE visitor = 1
GROUP BY name
ORDER BY visits DESC LIMIT 1;