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 &nb!
!
sp;
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 &nbs!
!
p; 1
B C 1 1 1 1
C A 1 1 1 1
C B 1 1 1 &n!
!
bsp; 1
C C 1 1 1 1
9 rows selected.
Erma Fernando <[EMAIL PROTECTED]>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 categoryCol3 is count of cases closed in the quarter
grouped by type and categoryCol4 is count of open cases at end of quarter
grouped by type and categoryCol1 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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!
