CODEWITHSUNDEEP
sqlsql-order-by
Thempower
Thempower

Reputation: 71

SQL order by and group by city

I'm having problems with a sql query I will first sketch the database structure:

  id   | userid | City
  ------------------------
  1    |   1    | New York
  2    |   1    | Washington
  3    |   1    | New York
  4    |   1    | Miami
  5    |   2    | New York
  4    |   1    | Ney York
  5    |   1    | Miami

This is how it looks, userid is linked to a user account.

What I like to get is the following: If I query for userid 1 I want to get a list of 5 most common cities of this user with it's counts; in this case it would be

  Counts  |  City
  ------------------------
  3       | New York
  2       | Miami
  1       | Washington

I already tried this:

     SELECT count(city), city FROM cities WHERE userid = '1' GROUP BY city ORDER BY count(city) DESC LIMIT 5

But this just gives a list with:

  Counts  |  City
  ------------------------
  1       | New York
  1       | New York
  1       | New York
  1       | Miami
  1       | Miami

Thanks in adventage!

Upvotes: 0

Views: 3314

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

Your query:

SELECT count(city), city
FROM cities
WHERE userid = '1'
GROUP BY city
ORDER BY count(city) DESC
LIMIT 5;

is correct for what you want to do. If you are getting the same city on different rows, then perhaps you have a data issue. For instance, perhaps there are unprintable characters after the city name that look like spaces, but are not. One way to tell is by delimiting the city name and looking at its length, something like:

SELECT count(city), concat('"', city, '"'), length(city)
FROM cities
WHERE userid = '1'
GROUP BY city
ORDER BY count(city) DESC
LIMIT 5;

Upvotes: 1

Yosi Dahari
Yosi Dahari

Reputation: 6999

You want to count id's..

SELECT count(id), city 
FROM cities 
WHERE userid = '1' 
GROUP BY city 
ORDER BY count(id) DESC 
LIMIT 5

Upvotes: 0

Related Questions