Two counts in one sql statement

#1

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.

#2

dn.perl (AT) gmail (DOT) com, 20.07.2009 17:39:

Quote:

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

Something like

SELECT country,
state
count(*) over (partition by country, state) as combo_count,
count(*) over (partition by country) as country_count
FROM info_table

#3

On Jul 20, 10:39*am, "dn.p... (AT) gmail (DOT) com" <dn.p... (AT) gmail (DOT) com> wrote:

Quote:

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

#4

On Jul 20, 11:04*am, ddf <orat... (AT) msn (DOT) com> wrote:

Quote:

On Jul 20, 10:39*am, "dn.p... (AT) gmail (DOT) com" <dn.p... (AT) gmail (DOT) 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

#5

dn.perl (AT) gmail (DOT) com schrieb:

Quote:

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.

To get the first (C/S combo) count - the simple aggregate should
suffice. To get the second (higher aggregation level - country) you need
as , already suggested, analytics. Both approaches can be as well
combined in one query ( i.e. - analytics can be used over aggregates ).

SQL> with t as (
2 select 'USA' country,'CA' state,'Los Angeles' city from dual
union all
3 select 'USA','CA','Sacramento' from dual union all
4 select 'USA','OR','Portland' from dual union all
5 select 'Canada','ONT','Ottawa' from dual
6 )
7 -- End test data
8 select country,state,count(*) "C/S combo",
9 sum(count(*)) over(partition by country) "Total (Country) count"
10 from t
11 group by country,state;

COUNTR STA C/S combo Total (Country) count
------ --- ---------- ---------------------
Canada ONT 1 1
USA CA 2 3
USA OR 1 3

Best regards

Maxim

--
Why make things difficult, when it is possible to make them cryptic
and totally illogical, with just a little bit more effort?

Aksel Peter Jørgensen

#6

On Jul 20, 11:39*pm, "dn.p... (AT) gmail (DOT) com" <dn.p... (AT) gmail (DOT) com> wrote:

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Two counts in one sql statement

comp.databases.oracle.misc comp.databases.oracle.misc

Two counts in one sql statement - 07-20-2009 , 10:39 AM

Re: Two counts in one sql statement - 07-20-2009 , 10:50 AM

Re: Two counts in one sql statement - 07-20-2009 , 11:04 AM

Re: Two counts in one sql statement - 07-20-2009 , 11:14 AM

Re: Two counts in one sql statement - 07-20-2009 , 04:19 PM

Re: Two counts in one sql statement - 07-31-2009 , 08:02 PM