Re: Two counts in one sql statement

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 20 Jul 2009 23:19:32 +0200
Message-ID: <4A64DF64.5040001_at_gmail.com>

dn.perl_at_gmail.com schrieb:
> 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

Received on Mon Jul 20 2009 - 16:19:32 CDT