473,327 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

count # of unioned values

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
2 2446
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Spark | last post by:
Hi, Situation: Need a query to return number of ticket records by month of open in a log table where the ticket open record is older than 24 hours then the ticket pending or ticket closed...
0
by: kovac | last post by:
The System.directoryservices.dll has an error, and this error was described in http://support.microsoft.com/default.aspx?scid=kb;en-us;839424 At the moment we have Framework version v1.0.3705 and...
22
by: Joseph Shraibman | last post by:
On a 7.3.4 database: explain analyse select count(*) from elog; Aggregate (cost=223764.05..223764.05 rows=1 width=0) (actual time=81372.11..81372.11 rows=1 loops=1) -> Seq Scan on elog ...
5
by: Cro | last post by:
Hello Access Developers, I'd like to know if it is possible to perform a count in an expression that defines a control source. My report is based on a query. In my report, I want a text box to...
9
by: Terry E Dow | last post by:
Howdy, I am having trouble with the objectCategory=group member.Count attribute. I get one of three counts, a number between 1-999, no member (does not contain member property), or 0. Using...
4
by: Danielle | last post by:
I have data like this in a two column temporary table - ID Age 23586 3 23586 3 23586 2 23586 2 23586 1 23586 1
1
by: sammy | last post by:
If you have a select with 2 attributes where you group by one attribute and do a count() for the second attribute, if the count() is 0 then that row is never displayed. How would you instead see...
22
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
2
by: Joe Powell | last post by:
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.