Help with query

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I've got multiple hotels listed in different towns in different countries in
a mysql database. When someone visits one, I add one to a field called

What I want to do is select out the most popular towns from the database
based on this number of clicks, but this query doesn't seem to work ..

SELECT towns, country
       from hotels
       GROUP BY town
       ORDER BY clicked desc
       LIMIT 0,5

I'm wondering whether I should have a nested select e.g.

       from hotels
       GROUP BY town
(SELECT * FROM hotels
       ORDER BY clicked desc

This is just an example, I haven't figured out how to do it yet, just
wondering what methods are available to me.


Re: Help with query

elyob wrote:
Quoted text here. Click to load it

Try this:

   SELECT SUM(h.clicked) AS Popularity,,
   FROM hotels AS h
   ORDER BY Popularity DESC
   LIMIT 5

When you use GROUP BY, you generally put some aggregate function in the
select-list.  The function is applied to the rows in your grouping.

In your GROUP BY clause, you should list all fields in the select-list
that don't appear in an aggregate function.  If you had listed only town
in the GROUP BY clause, you might get a group that combines hotels in
Paris, France with those in Paris, Texas.

And the value for country in your result set might be either "France" or
"USA", probably depending on the physical ordering of the rows in the
database, which is not dependable.

Bill K.

Re: Help with query

Quoted text here. Click to load it

Thanks for the example, in seconds of playing with that I am getting near
ideal results. Will study the accompanying text you posted as well. I think
that was far beyond I expected as a reply. Thankyou.


Site Timeline