By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,504 Members | 1,884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,504 IT Pros & Developers. It's quick & easy.

count # of unioned values

P: n/a
Suppose I have a table with 2 VARCHAR2(1)columns C1 and C2 and the
following 3 rows:
A B
A C
B A

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

I have written a PL/SQL procedure to do this. With Oracle 9.2 what is
the best method to do this with a single SQL query?
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi Joe,
this should work

compute sum label total of out1 out2 on report
break on report

SELECT COL, SUM(C1) as #1,SUM(C2) as #2
FROM
(SELECT COL1 AS COL,COUNT (*) AS C1,0 AS C2
FROM TEST
GROUP BY COL1
UNION
SELECT COL2 AS COL,0 AS C1,COUNT (*) AS C2
FROM TEST
GROUP BY COL2)
GROUP BY COL
/

HTH
Conan

Joe Powell wrote in message ...
Suppose I have a table with 2 VARCHAR2(1)columns C1 and C2 and the
following 3 rows:
A B
A C
B A

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

I have written a PL/SQL procedure to do this. With Oracle 9.2 what is
the best method to do this with a single SQL query?

Jul 19 '05 #2

P: n/a
jo********@lmco.com (Joe Powell) wrote in message news:<de**************************@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
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.