Oracle-L: Re: Help on creating this report (any method)

From: Sunny Verghese <vsgeorge70_at_yahoo.com>
Date: Fri, 04 Oct 2002 10:31:19 -0800
Message-ID: <F001.004E1157.20021004103119@fatcity.com>

Remember having done something similar some time ago... Check this out and see if this satisfies your requirement Query :

select type, category, sum(col1) col1, sum(col2) col2, sum(col3) col3, sum(col4) col4 from
(

select type, category, count(*) col1, 0 col2, 0 col3, 0 col4 from a group by type, category
union
select type, category, 0 col1, count(*) col2, 0 col3, 0 col4 from a group by type, category
union
select type, category, 0 col1, 0 col2, count(*) col3, 0 col4 from a group by type, category
union
select type, category, 0 col1, 0 col2, 0 col3, count(*) col4 from a group by type, category
)
group by type, category
/
Test

Table a

 Name                            Null?    Type
 ------------------------------- -------- ----
 TYPE                                     VARCHAR2(10)
 CATEGORY                                 VARCHAR2(10)
SQL> select * from a;
TYPE       CATEGORY   
---------- ---------- 
A          A          
A          B          
A          C          
B          A          
B          B          
B          C          
C          A          
C          B          
C          C          
A          A          
A          B          
A          C

12 rows selected.

Query result

TYPE CATEGORY COL1 COL2 COL3 COL4 ---------- ---------- ---------- ---------- ---------- ----------

A          A                   2          2          2          2
A          B                   2          2          2          2
A          C                   2          2          2          2
B          A                   1          1          1          1
B          B                   1          1          1          1
B          C                   1          1          1          1
C          A                   1          1          1          1
C          B                   1          1          1          1
C          C                   1          1          1          1

9 rows selected.

Erma Fernando wrote:
I have to create a tabular quarterly summary report, based on 4 different queries, but all are grouped by the same columns. Any suggestions on how to accomplish this - sql report etc. Thanks a lot.

Type category Col1 Col2 Col3 Col4

Elec Fac 500 100 200 400

Elec Rates 300 200 50 450

Elec Fran 200 100 50 250

Gas Fac 700 300 200 800

Gas Rates 900 100 600 400

Gas Fran 400 100 300 100

Col1 is count of open cases at start of quarter grouped by type and category

Col2 is count of new cases opened during quarter grouped by type and category

Col3 is count of cases closed in the quarter grouped by type and category

Col4 is count of open cases at end of quarter grouped by type and category

Col1 query is:

select type, category, count(*) form case where status='Open' and date_filed<'01-Jul-02' group by type, category;

Col2 query is:

select type, category, count(*) from case where date_filed>='01-Jul-02' group by type, category;

Col3 query is

select type, category, count(*) form case where status='Closed' and date_closed>='01-Jul-02' and date_closed<='03-Sep-02' group by type, category;

Col4 query is

select type, category, count(*) form case where status='Open' group by type, category;

Chat with friends online, try MSN Messenger: Click Here

-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erma Fernando INET: cecfernan@hotmail.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).


---------------------------------

Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sunny Verghese
  INET: vsgeorge70_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Fri Oct 04 2002 - 13:31:19 CDT