RE: Query Question
- From: "Hostetter, Jay M" <JHostetter@xxxxxxxxxxxxxxxxxxxx>
- To: <Oracle-L@xxxxxxxxxxxxx>
- Date: Fri, 11 Aug 2006 08:53:24 -0400
Thanks to everybody that responded. I ended up using a modified version of a query that was suggest by Mark: 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; ________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Hostetter, Jay M Sent: Thursday, August 10, 2006 4:08 PM To: Oracle-L@xxxxxxxxxxxxx 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 (customer_name, 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.