Re: Using multiple group functions in a single query

Thanks John.

That seems like an awful lot of trouble to get the result. Is there no way to do it without creating views?

-Mia

John Gilson wrote:

> 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),
> PRIMARY KEY (state, county)
> )
>
> CREATE TABLE governors
> (
> state CHAR(2) NOT NULL PRIMARY KEY,
> name VARCHAR(32) NOT NULL
> )
>
> CREATE VIEW state_pops
> AS
> SELECT state, SUM(pop) AS state_pop
> FROM county_pops
> GROUP BY state
>
> CREATE VIEW max_state_pop
> AS
> SELECT MAX(state_pop) AS pop
> FROM state_pops
>
> -- This will now get you what you're looking for
> SELECT sp.state, g.name, sp.state_pop
> FROM state_pops AS sp
> INNER JOIN
> max_state_pop AS msp
> ON msp.pop = sp.state_pop
> INNER JOIN
> governors AS g
> ON g.state = sp.state
>
> Hope this helps.
>
> 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 Wed Aug 28 2002 - 19:38:55 CEST