Re: Using multiple group functions in a single query

I use SQL Server but I solved this in a vanilla way since I'm not sure what DBMS you're using.

CREATE TABLE county_pops
(

county  VARCHAR(32)     NOT NULL,
state   CHAR(2)         NOT NULL,
pop     INT             NOT NULL CHECK (pop > 0),

state   CHAR(2)         NOT NULL PRIMARY KEY,
name    VARCHAR(32)     NOT NULL

Regards,
jag

"Mia" <mmiaa_at_cox.net> wrote in message news:3D6A8760.37F2E2A4_at_cox.net...
> Hi all,
>
> I'm trying to figure out how to get a report showing the max() of a
> sum(). Here's a simplified case:
>
> The following table contains the populations of each county of each
> state:
> create table county (cname char(32), state char(2), population number);
>
> The following table contains the governor name of each state:
> create table governor (gname char(32), state char(2));
>
> I want a query that reports the state, governor, and total population of
> the single most populated state. The following query gives me the
> population and governor of each state:
>
> select c.state, g.gname, sum(c.population) from county c, governor g
> group by c.state, g.gname;
>
> and I can certainly order the output, but how can I get only the highest
> result? What I think I need is a max() function somehow wrapping this,
> but I can't figure out to do it.
>
> Any help would be appreciated.
>
> -Mia
>
>
Received on Mon Aug 26 2002 - 23:16:54 CEST