Re: Two counts in one sql statement

From: ddf <oratune_at_msn.com>
Date: Mon, 20 Jul 2009 09:04:10 -0700 (PDT)
Message-ID: <12ed2b6f-1c05-49fe-9263-609be8654e2c_at_c2g2000yqi.googlegroups.com>

On Jul 20, 10:39 am, "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.
Analytics are your friend:

SQL> create table city_state(

  2          country varchar2(30),
  3          state   varchar2(30),
  4          city    varchar2(80)

  5 );

Table created.

SQL>
SQL> insert all
  2 into city_state
  3 values('USA','CA','Los Angeles')
  4 into city_state
  5 values('USA','CA','Sacramento')
  6 into city_state
  7 values('USA','OR','Portland')
  8 into city_state
  9 values('Canada','ONT','Ottawa')
 10 select * from dual;

4 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select country, state, count(*) over (partition by state) city_ct, count(*) over (partition by country) state_ct   2 from city_state
  3 where country = 'USA'
  4 order by 2;

COUNTRY                        STATE

CITY_CT STATE_CT

------------------------------ ------------------------------
---------- ----------
USA                            CA
2          3
USA                            CA
2          3
USA                            OR
1          3

SQL>
SQL> select country, state, count(*) over (partition by state) city_ct, count(*) over (partition by country) state_ct   2 from city_state
  3 order by country desc, state;

COUNTRY                        STATE

CITY_CT STATE_CT

------------------------------ ------------------------------
---------- ----------
USA                            CA
2          3
USA                            CA
2          3
USA                            OR
1          3
Canada                         ONT
1          1

SQL> David Fitzjarrell Received on Mon Jul 20 2009 - 11:04:10 CDT