Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query Question

RE: Query Question

From: Bobak, Mark <>
Date: Thu, 10 Aug 2006 16:18:30 -0400
Message-ID: <>

How about this:  

select acc_sum.customer_name, acc_sum.total_amount, det.detail_item from (select account_no, customer_name, sum(amount) total_amount from accounts) acc_sum,

        details det
where acc_sum.account_no = det.account_no;    

Mark J. Bobak 
Senior Oracle Architect 
ProQuest Information & Learning 

Ours is the age that is proud of machines that can think and suspicious
of men who try to.  --H. Mumford Jones, 1892-1980



[] On Behalf Of Hostetter, Jay M
Sent: Thursday, August 10, 2006 4:08 PM
Subject: Query Question

I know there has to be a simple way to do this, but my brain can't put
it togther this afternoon.  I have a parent table (accounts) and a child
table (details).  I am creating a view that ties the two tables
together.  I would like a column in this view that shows a summary of a
column in the parent table - but I want this summary to be for each
distinct parent record - not the child records.
Here is my view:
create view test_view 

amount, detail_item) as
(select customer_name,
sum(amount), detail_item from accounts a, details d where a.account_no=d.account_no group by customer_name; Sample records from Accounts (Customer_name, Account_no, Amount): Fred, 12345, 10 Fred, 45678, 15 Sample records from Details (Account_No, Detail_item, Description): 12345, Telephone, yaddayadda 12345, Call Forwarding, yaddayadda 45678, Cell Phone, yaddayadda I would like my view to return (note the 25, which is a sum of the two disting Amount values from Accounts): Fred, 25, Telephone Fred, 25, Call Forwarding Fred, 25, Cell Phone Instead, I am getting (note the 35, which is the sum of the amount field, when joined with details, because the value 10 is duplicated due to 2 child records for acocount 12345): Fred, 35, Telephone Fred, 35, Call Forwarding Fred, 35, Cell Phone In my view, the sum(amount) is summing for each row in the view. I want it to sum for each distinct account number. I know that I can use sum(distinct amount), but there is no guarantee that the amounts will always be unique - the PK is the account number. Thank you, Jay **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business. --
Received on Thu Aug 10 2006 - 15:18:30 CDT

Original text of this message