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