[RESOLVED] A hard sql query :-(

[errorCode30]

Hi guys,

I'm having a problem trying to work out how to do the following. (I dont know if it should be done in 2 parts -- get the first bit, then do a second query?)

I basicly have a table like this:

UserName - Location
Bob      - UK
Jack     - USA
Mark     - UK
Tom      - France
Paul     - USA
Sue      - UK
Debbie   - UK

And what i would like to do, is somehow get the top 5 Locations, and the number of times each appears. As below:

UK - 4
USA - 2
France - 1

I'm thought i would need to use "Distinct" -- to actually get all the locations (which i do not have a record of), then run each through a Count to get the totals??

But is there anyway to do it with one mysql query? instead of a php while loop and lots of queries?

Many thanks.

[rulian]

Something along the lines of
SELECT location, count(location) FROM mytable GROUP BY location

[errorCode30]

Thanks so much rulian!!!

I cannot belive how simple you've made this look. lol.

Thanks again for the great answer. 🙂

[prabhush]

select location location,count(location) count from t3 group by location having count(location)>=4 order by count desc

if you execute above query it will take the top count of the location.