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