Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle SQL: how to summarize detail records into multiple header fields

Posted on 2004-09-24
4
Medium Priority
?
850 Views
Last Modified: 2008-01-09
I have two tables:

Table A has the following fields
  Key
  Total1
  Total2
  Total3

Table B has the following fields
  Key
  Date
  Status

Tables A and B are joined with the field "Key". Table B has multiple records for each given value in "Key". There is a "one-to-many" relationship between A and B.

How do I accumulate values in A per the following rules?

For each record in table A
  Read all records in table B that match A.Key = B.Key
     If B.Status = "A" then increment A.Total1 by 1
     If B.Status = "B" then increment A.Total2 by 1
     If B.Status = "C" then increment A.Total3 by 1

The following shows the resulting tables

A.Key  A.Total1     A.Total2      A.Total3
AAAA      3                 2                0
BBBB       2                 1                2
CCCC      0                 0                0

B.Key  B.Date          B.Status
AAAA 01/01/2001         A
AAAA 01/02/2001         B
AAAA 01/03/2001         A
AAAA 01/04/2001         B
AAAA 01/05/2001         A
BBBB  02/01/2001         C
BBBB  02/02/2001         A
BBBB  03/01/2001         A
BBBB  04/01/2001         B
BBBB  05/01/2001         C

Can this be done with SQL?

Thanks,

Jim
0
Comment
Question by:jrollins138
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
andrewst earned 600 total points
ID: 12144440
update a set (total1, total2, total3) =
(
select sum(decode(b.status,'A',1,0))
, sum(decode(b.status,'B',1,0))
, sum(decode(b.status,'C',1,0))
from b where b.key = a.key
);
0
 
LVL 6

Assisted Solution

by:Jankovsky
Jankovsky earned 600 total points
ID: 12144457
Update A set A.Total1=NVL(A.Total1,0)+(select count(1) from B where A.KEY=B.KEY and B.STATUS='A')
 ,A.Total2=NVL(A.Total1,0)+(select count(1) from B where A.KEY=B.KEY and B.STATUS='B')
 ,A.Total3=NVL(A.Total1,0)+(select count(1) from B where A.KEY=B.KEY and B.STATUS='C')
0
 
LVL 15

Expert Comment

by:andrewst
ID: 12144554
Sorry, my previous answer overwrites ratehr than increments the totals.  SHould be:

update a set (total1, total2, total3) =
(
select a.total1+sum(decode(b.status,'A',1,0))
, a.total2+sum(decode(b.status,'B',1,0))
, a.total3+sum(decode(b.status,'C',1,0))
from b where b.key = a.key
);
0
 

Author Comment

by:jrollins138
ID: 12145042
Well done and many thanks to both of you. I've increased the points to 300 and would like to split them equally between you. I used elements from both answers to solve my problem.

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question