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.

Anyone think of another way to do this in a single
SQL statement, or some other simple method?
--
Skip Evans
Big Sky Penguin, LLC
61 W Broadway
Butte, Montana 59701
406-782-2240

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

Re: Using MAX with COUNT?

------=_Part_155241_23064718.1153602852687
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Why don't you use a where instead of a group function?

On 7/22/06, Skip Evans wrote:
>
> 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.
>
> Anyone think of another way to do this in a single
> SQL statement, or some other simple method?
> --
> Skip Evans
> Big Sky Penguin, LLC
> 61 W Broadway
> Butte, Montana 59701
> 406-782-2240
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>


--
Dave W

------=_Part_155241_23064718.1153602852687--

RE: Using MAX with COUNT?

SELECT count(*) FROM `bsp_area` GROUP BY boroughID


Bastien


>From: Skip Evans
>To: Php-Db
>Subject: [PHP-DB] Using MAX with COUNT?
>Date: Sat, 22 Jul 2006 13:48:43 -0600
>
>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.
>
>Anyone think of another way to do this in a single SQL statement, or some
>other simple method?
>--
>Skip Evans
>Big Sky Penguin, LLC
>61 W Broadway
>Butte, Montana 59701
>406-782-2240
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
>

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

Re: Using MAX with COUNT?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Skip Evans wrote:
> 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.
>
> Anyone think of another way to do this in a single SQL statement, or
> some other simple method?

select boroughID, count(*) as thecount from bsp_area group by boroughID
order by thecount desc limit 1

- -Stut
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEw2YG2WdB7L+YMm4RAqkyAJwIHMzDeDNBanlDpjARF4ElRasBYwCg mpIl
HGbI5JrIlTPf0c5r6Tg6+o8=
=jRmZ
-----END PGP SIGNATURE-----

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

RE: Using MAX with COUNT?

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

Bastien

>From: sevans@bigskypenguin.com
>To: "Bastien Koert"
>CC: skip@bigskypenguin.com, php-db@lists.php.net
>Subject: RE: [PHP-DB] Using MAX with COUNT?
>Date: Sat, 22 Jul 2006 20:25:57 -0600 (MDT)
>
>Hi Bastien and all,
>
>Bastien wrote:
> > SELECT count(*) FROM `bsp_area` GROUP BY boroughID
>
>...which returns all the counts, but I'm only interested in the one with
>the most areas, so I did it like this:
>
>SELECT count( * )
>FROM `bsp_area`
>GROUP BY boroughID ORDER BY count(*) DESC
>
>...which forces the borough with the most areas to the top, so I can just
>grab the first row. Probably not the best way to do it since ideally I'd
>like only one row in the return set, but it gets the job done.
>
>If anyone has a suggestion to return just the row with the most areas that
>would be great.
>
>Skip
>
>
>
> >
> >
> > Bastien
> >
> >
> >>From: Skip Evans
> >>To: Php-Db
> >>Subject: [PHP-DB] Using MAX with COUNT?
> >>Date: Sat, 22 Jul 2006 13:48:43 -0600
> >>
> >>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.
> >>
> >>Anyone think of another way to do this in a single SQL statement, or
>some
> >>other simple method?
> >>--
> >>Skip Evans
> >>Big Sky Penguin, LLC
> >>61 W Broadway
> >>Butte, Montana 59701
> >>406-782-2240
> >>
> >>--
> >>PHP Database Mailing List (http://www.php.net/)
> >>To unsubscribe, visit: http://www.php.net/unsub.php
> >>
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>
>

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