Re: Using multiple group functions in a single query

If you want it all in one self-contained SELECT, one way of doing it is the following:

SELECT max_state_pop.state, g.name, max_state_pop.pop FROM (SELECT state, SUM(pop) AS pop

              FROM county_pops
              GROUP BY state
              HAVING SUM(pop) >= ALL (SELECT SUM(pop)
                                                                FROM
county_pops
                                                                GROUP BY
state)) AS max_state_pop
            INNER JOIN
            governors AS g
            ON g.state = max_state_pop.state

Regards,
jag

"Mia" <mmiaa_at_cox.net> wrote in message news:3D6D0AFD.F795AE42_at_cox.net...
> 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 - 21:59:09 CEST