Help with query

[elyob]

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
'clicked'.

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.

SELECT *
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.

Thanks

[Bill Karwin]

Re: Help with query

elyob wrote:[color=blue]
> 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[/color]

Try this:

SELECT SUM(h.clicked) AS Popularity, h.town, h.country
FROM hotels AS h
GROUP BY h.town, h.country
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.

Regards,
Bill K.

[elyob]

Re: Help with query


"Bill Karwin" <bill@karwin.co m> wrote in message
news:d1vatf02po e@enews2.newsgu y.com...[color=blue]
> elyob wrote:[color=green]
>> 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[/color]
>
> Try this:
>
> SELECT SUM(h.clicked) AS Popularity, h.town, h.country
> FROM hotels AS h
> GROUP BY h.town, h.country
> ORDER BY Popularity DESC
> LIMIT 5
>[/color]

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.

Nick