Re: Two counts in one sql statement

From: kevin <majunyue_at_hotmail.com>
Date: Fri, 31 Jul 2009 18:02:40 -0700 (PDT)
Message-ID: <e6ade063-61bf-4e84-bb3d-377d54dce356_at_i4g2000prm.googlegroups.com>

On Jul 20, 11:39 pm, "dn.p..._at_gmail.com" <dn.p..._at_gmail.com> wrote:
> I have a table with 3 fields.
>
> country   state   city
> USA        CA     Los Angeles
> USA        CA      Sacramento
> USA        OR     Portland
> Canada    ONT   Ottawa
>
> Could I find total count for the US and counts for country-state combo
> with one sql statement. I doubt whether this is possible. The output
> should look like:
> country   state    C/S combo   Total C(ountry) count
>
> USA        CA          2                  3   (2 in CA + 1 in OR)
> USA        OR          1                  3
>
> TIA.
select bb.*, sum(state_num) over(partition by country) country   from (select country, state, count(*) state_num

          from tt
         group by country, state) bb

Received on Fri Jul 31 2009 - 20:02:40 CDT