Suppose I have a table with 2 VARCHAR2(1)colu mns 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? 2 2461
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)colu mns 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? jo********@lmco .com (Joe Powell) wrote in message news:<de******* *************** ****@posting.go ogle.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 record. Tickets can also only have a
closed record with no open record.
Sample data table:
ticket_id date_log status_name status_id
|
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 I have remove
System.directoryservices.dll from current Framework version v1.0.3705. In
state of old version I load die new...
|
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 (cost=0.00..203012.24 rows=8300724 width=0)
(actual time=3.91..71542.53 rows=8313762 loops=1)
Total runtime: 81378.42 msec
|
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
display the number of times a certain value appears in a certain field
(i.e. perform a ‘count'). I will be doing this for many values in many...
|
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 LDIFDE as a comparison I get the same
results. No members means just that, an empty group. Zero means that the
DirectorySearcher.SizeLimit has been...
| |
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
|
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 a line item
where you list the attribute with 0 as the count.
|
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=" & msDbFilename
moConn.Properties("Persist Security Info") = False
moConn.ConnectionString = msConnString
moConn.CursorLocation = adUseClient...
|
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 column such as:
Value #C1s #C2s
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |