SQL Language (MOSC)

MOSC Banner

SUM of master & child columns in 1-to-many join

edited Jan 14, 2014 5:25AM in SQL Language (MOSC) 3 commentsAnswered ✓
 One of my users wants to tally a number of financial transactions (received_amount's) from master query, along with the sum of the applied_amount's from a child table. I'm trying to provide this to him in a single query, without using a sub-query.
If I use a simple sum on the master column, the result will be skewed as the master row is duplicated for any multiple occurrence of the child row:

SELECT
   SUM(DISTINCT RECEIVED_AMOUNT),
   SUM(DETAIL.APPLIED_AMOUNT)
FROM
   MASTER,
   DETAIL
WHERE
   MASTER.OFFICE_ID='0001' AND
   MASTER.MASTER_ID = DETAIL.MASTER_ID;

DISTINCT is not usable as this would remove any master rows that have the same amount.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center