Re: Two counts in one sql statement
Date: Mon, 20 Jul 2009 09:14:13 -0700 (PDT)
Message-ID: <9bea17f0-5e3d-4b62-9382-1225b5ab25e6_at_y19g2000yqy.googlegroups.com>
On Jul 20, 11:04 am, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Either query will return correct results for the given data:
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 country,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>
SQL> select country, state, count(*) over (partition by country,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>
SQL> insert all
2 into city_state
3 values('USA','OK','Enid')
4 into city_state
5 values('USA','PA','Pittsburg')
6 into city_state
7 values('USA','OH','Cleveland')
8 into city_state
9 values('Canada','ONT','London')
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 6 USA CA 2 6 USA OH 1 6 USA OK 1 6 USA OR 1 6 USA PA 1 6
6 rows selected.
SQL>
SQL> select country, state, count(*) over (partition by country,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 6 USA CA 2 6 USA OH 1 6 USA OK 1 6 USA OR 1 6 USA PA 1 6
6 rows selected.
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 6 USA CA 2 6 USA OH 1 6 USA OK 1 6 USA OR 1 6 USA PA 1 6 Canada ONT 2 2 Canada ONT 2 2
8 rows selected.
SQL>
SQL> select country, state, count(*) over (partition by country,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 6 USA CA 2 6 USA OH 1 6 USA OK 1 6 USA OR 1 6 USA PA 1 6 Canada ONT 2 2 Canada ONT 2 2
8 rows selected.
SQL> David Fitzjarrell Received on Mon Jul 20 2009 - 11:14:13 CDT