Message-ID: <44C2811B.6010300@bigskypenguin.com>
Date: Sat, 22 Jul 2006 13:48:43 -0600
From: Skip Evans <skip@bigskypenguin.com>
MIME-Version: 1.0
To: Php-Db <php-db@lists.php.net>
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 <g>
Cheers - Neil