Re: Intermediate SQL problem

Ugly yes but it should work...

select g.st_name as State, a.total_population, c.total_male, d.total_female, b.total_selected, e.selected_Male, f.selected_female from
  (select st_abbrev, count(rowid) as total_population from population group by st_abbrev) a,
  (select st_abbrev, count(rowid) as total_selected from population group by st_abbrev having selected = 'Y') b,
  (select st_abbrev, count(rowid) as total_male from population group by st_abbrev having gender = 'M') c,
  (select st_abbrev, count(rowid) as total_female from population group by st_abbrev having gender = 'F') d,
  (select st_abbrev, count(rowid) as selected_male from population group by st_abbrev having gender = 'M' and selected = 'Y') e,   (select st_abbrev, count(rowid) as selected_female from population group by st_abbrev having gender = 'F' and selected = 'Y') f,   state g
where a.st_abbrev = b.st_abbrev and

            a.st_abbrev = c.st_abbrev and
            a.st_abbrev = d.st_abbrev and
            a.st_abbrev = e.st_abbrev and
            a.st_abbrev = f.st_abbrev and
            a.st_abbrev = g.st_abbrev

<nameproliferation_at_my-deja.com> wrote in message news:82mirm$ql1$1_at_nnrp1.deja.com...
> Hello,
>
> I'm hoping someone can help me formulate the SQL I need to do this
> report.
>
> I have to calculate statistics off a single table, listed by state, on
> another table:
>
> STATE ( st_abbrev, st_name );
> POPULATION ( process_id, st_abbrev, gender, err_code, selected );
>
> I need the count, by state, of each of the field values. The report
> header looks like:
>
> Process: XXX
> State Total Population Total Male Total Female Total Selected \
> ===== ================ ========== ============ ============== \
>
> Total Male Selected Total Female Selected Error Code Error Total
> =================== ===================== ========== ===========
>
> Note that the last 2 fields are really a table in a table!
>
> Sheesh!
>
> I think this can be done in 1 select (esp. if the error part is
> dropped), but I'm having a real problem. Can someone help!??
>
> Thanks much!
> -=np=-
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Dec 08 1999 - 22:40:26 CST