Re: count # of unioned values

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 - 13:40:28 CET