SUM of master & child columns in 1-to-many join
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.
0