Configure MySQL with a separate account

Question

I have a select query that produces the following:

  select customers.city, books.title                                                                   
 from loaned, books, customers                                                                         
 where loaned.userID = customers.userID                                                                
 and loaned.bookID = books.bookID 
 + ------------ + ------------------------------- +
 |  city ​​|  title |
 + ------------ + ------------------------------- +
 |  Harrogate |  The cross rabbit |
 |  Harrogate |  PHP and MySQL web development |
 |  Harrogate |  PHP and MySQL web development |
 |  Whitehaven |  Greek Mythology |
 |  Whitehaven |  Dino-soaring |
 |  Whitehaven |  Dino-soaring |
 |  Sale |  Magic tricks |
 |  Sale |  Magic tricks |
 |  Sale |  Magic tricks |
 |  Sale |  Dino-soaring |
 |  Sale |  Dino-soaring |
 + ------------ + ------------------------------- +
 11 rows in set (0.00 sec)

I want to find the most popular heading for each city, so I did the following:

  group by city
 order by count (distinct title) desc

however, this does not produce the correct results. I get:

  + ------------ + ------------------------------- +
 |  city ​​|  title |
 + ------------ + ------------------------------- +
 |  Sale |  Dino-soaring |
 |  Whitehaven |  Dino-soaring |
 |  Harrogate |  PHP and MySQL web development |
 + ------------ + ------------------------------- +
 3 rows in set (0.00 sec)

This is sorted alphabetically, not by popularity. Having received the data, I thought it would be easy to order it as I needed, but it didn’t work out that way. Do I need to make some kind of connection or something more complex?

Thanks in advance.

Answer 1

Thanks to all who responded. Since this was a test question, I did not want to “cut and paste” someone else, but I used my own logic for my own request. Here is what I got:

  select city, title
 from (
     select customers.city as city, books.title as title, count (books.title) as cnt
     from books, customers, loaned
     where loaned.userID = customers.userID
     and loaned.bookID = books.bookID
     group by title, city
     order by cnt desc) as tbl
 group by city

Results:

  + ------------ + ------------------------------- +
 |  city ​​|  title |
 + ------------ + ------------------------------- +
 |  Harrogate |  PHP and MySQL web development |
 |  Sale |  Magic tricks |
 |  Whitehaven |  Dino-soaring |
 + ------------ + ------------------------------- +
 3 rows in set (0.00 sec)

Answer 2

Try replacing distinct title with title and this should solve your problem.

Answer 3

I would approach this problem in 3 steps. First get an account of each book from each city.

 select customers.city, books.title, count(books.title) as count from loaned, books, customers where loaned.userID = customers.userID and loaned.bookID = books.bookID group by customers.city, books.title 

This query will return the following lines.

 +------------+-------------------------------+-------+ | city | title | count | +------------+-------------------------------+-------+ | Harrogate | The cross rabbit | 1 | | Harrogate | PHP and MySQL web development | 2 | | Whitehaven | Greek Mythology | 1 | | Whitehaven | Dino-soaring | 2 | | Sale | Magic tricks | 3 | | Sale | Dino-soaring | 2 | +------------+-------------------------------+-------+ 

Using this data, I will use this to make a group for each city with the largest number.

 select city, max(count) as count from ( select customers.city , books.title, count(books.title) as count from loaned, books, customers where loaned.userID = customers.userID and loaned.bookID = books.bookID group by customers.city, books.title ) as city_book_max_count group by city 

What will return these lines

 +------------+-------+ | city | count | +------------+-------+ | Harrogate | 2 | | Whitehaven | 2 | | Sale | 3 | +------------+-------+ 

Using the data from the two tables, we can join them by city and account to get the corresponding books that correspond to both tables.

 select city_book_count.city, city_book_count.title from ( select customers.city , books.title, count(books.title) as count from loaned, books, customers where loaned.userID = customers.userID and loaned.bookID = books.bookID group by customers.city, books.title ) as city_book_count join ( select city, max(count) as count from ( select customers.city , books.title, count(books.title) as count from loaned, books, customers where loaned.userID = customers.userID and loaned.bookID = books.bookID group by customers.city, books.title ) as city_book_count_temp group by city ) as city_book_max_count on city_book_count.city = city_book_max_count.city and city_book_count.count = city_book_max_count.count 

Answer 4

I am deleting the client table since there is no result from this table

 select customers.city , books.title , count(books.title) Total from loaned, books, customers where loaned.userID = customers.userID and loaned.bookID = books.bookID group by customers.city , books.title order by 3 desc