Order by and Count

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

Threaded View

I have been trying to do a similar thing for a number of different
tables but have given up as i could not find a way to do it but it
seems an obvious thing many pople would want to do so i thought i would
ask once more to see if it is possible.

I have a mysql table like this
Id01  Maidstone     KENT
Id02  Ashford         KENT
Id03  Bristol           AVON
Id04  Westminster  LONDON
Id05  Bath             AVON
Id06  Chatham       KENT

Is there a way to order the table by the county and get total for the
number of rows fro meach county (for output to a coldfusion page) e.g.
KENT (3)
AVON (2)

Though formating does not matter



Re: Order by and Count

The easiest way is to do it in two steps.  The first step is to make a
select that gives you all the different counties and the number they
appear.  The second step is to overloop this first select and find all
the records that are belonging to the first.

This selects finds all the different counties and makes a count of it
ordered by quantity descending :

select    distinct(county), count(*)
from    cities
group by county
order by count(*) desc

Then you walk through the results of this select and you find the
records from KENT, ...

Site Timeline