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: Conan <conan_at___no-spam__dublin.com>
Date: Thu, 5 Feb 2004 11:10:25 -0000
Message-ID: <RwpUb.1509$rb.55215@news.indigo.ie>

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?
Received on Thu Feb 05 2004 - 05:10:25 CST

Original text of this message

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