Home » SQL & PL/SQL » SQL & PL/SQL » Totals using decode
Totals using decode [message #8749] Tue, 23 September 2003 12:13 Go to next message
Jean
Messages: 16
Registered: September 1999
Junior Member
I have the following tables:

CREATE TABLE t5 (event NUMBER, call DATE, type VARCHAR2(6));
CREATE TABLE t6 (event NUMBER, responder VARCHAR2(4), response DATE);
INSERT INTO t5 VALUES (1,TO_DATE('08/01/2003 07:45AM','MM/DD/YYYY HH12:MIAM'),'FEABA1');
INSERT INTO t5 VALUES (2,TO_DATE('08/01/2003 07:55AM','MM/DD/YYYY HH12:MIAM'),'FEABA2');
INSERT INTO t5 VALUES (3,TO_DATE('08/01/2003 08:15AM','MM/DD/YYYY HH12:MIAM'),'FEABA1');
INSERT INTO t5 VALUES (4,TO_DATE('08/01/2003 03:00PM','MM/DD/YYYY HH12:MIAM'),'FEABC1');
INSERT INTO t5 VALUES (5,TO_DATE('08/01/2003 03:00PM','MM/DD/YYYY HH12:MIAM'),'FEBAC2');
INSERT INTO t5 VALUES (6,TO_DATE('08/01/2003 03:00PM','MM/DD/YYYY HH12:MIAM'),'FEASA1');
INSERT INTO t5 VALUES (7,TO_DATE('08/01/2003 03:00PM','MM/DD/YYYY HH12:MIAM'),'FEABD4');
INSERT INTO t5 VALUES (8,TO_DATE('08/01/2003 03:00PM','MM/DD/YYYY HH12:MIAM'),'FEB1');
INSERT INTO t5 VALUES (9,TO_DATE('08/01/2003 03:00PM','MM/DD/YYYY HH12:MIAM'),'FEABB1');
INSERT INTO t5 VALUES (10,TO_DATE('08/01/2003 03:00PM','MM/DD/YYYY HH12:MIAM'),'FEA4');
INSERT INTO t5 VALUES (11,TO_DATE('08/01/2003 03:00PM','MM/DD/YYYY HH12:MIAM'),'FFA4A1');
INSERT INTO t5 VALUES (12,TO_DATE('08/01/2003 03:00PM','MM/DD/YYYY HH12:MIAM'),'FFA4B1');
INSERT INTO t6 VALUES (1,'A001',TO_DATE('08/01/2003 07:47AM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (1,'M034',TO_DATE('08/01/2003 07:59AM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (2,'A001',TO_DATE('08/01/2003 07:56AM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (2,'A002',TO_DATE('08/01/2003 07:56AM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (3,'E001',TO_DATE('08/02/2003 08:15AM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (3,'L001',TO_DATE('08/02/2003 08:16AM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (4,'A002',TO_DATE('08/01/2003 03:16PM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (4,'A001',TO_DATE('08/01/2003 03:16PM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (4,'L001',TO_DATE('08/01/2003 03:16PM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (5,'L001',TO_DATE('08/01/2003 03:16PM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (6,'M034',TO_DATE('08/01/2003 03:16PM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (7,'M034',TO_DATE('08/01/2003 03:16PM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (8,'M001',TO_DATE('08/01/2003 03:16PM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (8,'A005',TO_DATE('08/01/2003 03:16PM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (9,'A005',TO_DATE('08/01/2003 03:16PM','MM/DD/YYYY HH12:MIAM'));
INSERT INTO t6 VALUES (10,'A005',TO_DATE('08/01/2003 03:16PM','MM/DD/YYYY HH12:MIAM'));

The following select: Get number of responses by last 2 chars of t1.type if 6 characters else group others together for t1.type = 'FE%'.
*********************************
select unit, level, ID, A
from (SELECT decode (length (t5.type), 6, substr(t5.type,5,2),
4, 'Others') level,
t6.responder unit,
replace(t6.responder,substr(t6.responder, -3),'') ID,
count(decode (length (t5.type), 6, substr(t5.type,5,2),
4, 'Others')) A
FROM t5, t6
WHERE t5.event = t6.event
AND substr(t5.type,1,2) = 'FE'
GROUP BY replace(t6.responder,substr(t6.responder, -3),''),
t6.responder,
decode (length (t5.type), 6, substr(t5.type,5,2),
4, 'Others'))
order by ID, unit, level

The following output:
************************
UNIT LEVEL ID A
---- ------ ---- ---------
A001 A1 A 1
A001 A2 A 1
A001 C1 A 1
A002 A2 A 1
A002 C1 A 1
A005 B1 A 1
A005 Others A 2
E001 A1 E 1
L001 A1 L 1
L001 C1 L 1
L001 C2 L 1
M001 Others M 1
M034 A1 M 2
M034 D4 M 1

14 rows selected.

I can get total by t2.responder. How do I get totals by t1.type.

A1 A2 B1 C1 C2 D4 Others Total
---------------------------------------------------------------------
A001 1 1 1 3
A002 1 1 2
A005 1 2 3
E001 1 1
L001 1 1 1 3
M001 1 1
M0034 2 1 3

???????????????????????????????????????????????????????
--------------------------------------------------------------
TypeTot 5 2 1 3 1 1 3 16

Report message to a moderator

Oops! Submitted twice. Re: Totals using decode [message #8758 is a reply to message #8749] Wed, 24 September 2003 07:20 Go to previous message
Jean
Messages: 16
Registered: September 1999
Junior Member
Same as Calculating Totals previously submitted on 9/23.

Report message to a moderator

Previous Topic: need queries difference.
Next Topic: select count(*) into a variable.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 23 12:36:54 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.