Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: count # of unioned values

Re: count # of unioned values

From: kibeha <kibeha_at_post6.tele.dk>
Date: 5 Feb 2004 04:40:28 -0800
Message-ID: <444b180d.0402050440.464dd9aa@posting.google.com>

joe.powell_at_lmco.com (Joe Powell) wrote in message news:<deea9325.0402041357.3037f269_at_posting.google.com>...
> For each value in columns C1 and/or C2, I need to report the count of
> that value in each column such as:
>
> Value #C1s #C2s
> ----- ----- -----
> A 2 1
> B 1 1
> C 0 1
> ----- ----- -----
> Total 3 3

This should do it :

select

   value,
   sum(decode(col,'C1',cnt,0)) cnt_c1,
   sum(decode(col,'C2',cnt,0)) cnt_c2
from
(

   select 'C1' col, c1 value, count(*) cnt    from table1 group by c1
   union all
   select 'C2' col, c2 value, count(*) cnt    from table1 group by c2
) s1
group by value

KiBeHa Received on Thu Feb 05 2004 - 06:40:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US