Re: Subject: Using MAX with COUNT?

>Message-ID: <44C2811B.6010300@bigskypenguin.com>
>Date: Sat, 22 Jul 2006 13:48:43 -0600
>From: Skip Evans
>MIME-Version: 1.0
>To: Php-Db
>Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>Content-Transfer-Encoding: 7bit
>Subject: Using MAX with COUNT?
>
>Hey all,
>
>I have a table like this:
>
>boroughID Area
>========= ====
>1 Chelsea
>1 East Village
>1 West Village
>1 So Ho
>2 Prospect Park
>2 Brooklyn Heights
>3 Prospect Heights
>
>What I want to know is which boroughID has the most area's assocated
>with it, and how many.
>
>So I tried this:
>
>SELECT max(count(*)) FROM `bsp_area` GROUP BY boroughID
>
>...and got an "Invalid use of group function" error


.... Because you need to do count(boroughID) not count(*)


>Anyone think of another way to do this in a single SQL statement, or
>some other simple method?


SELECT count(boroughID) AS total
FROM `bsp_area`
GROUP BY boroughID
ORDER BY total DESC
LIMIT 1

... ought to do it

Cheers - Neil

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php