CODEWITHSUNDEEP
sqldatabaserelational-database
Martin J
Martin J

Reputation: 63

SQL query to count most "popular" value?

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)?

Upvotes: 5

Views: 8561

Answers (1)

HorusKol
HorusKol

Reputation: 8706

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;

Upvotes: 8

Related Questions