Here are the 3 separate tables with data.
Table Adj_Orig
ID LINE_NUM DOLLAR_AMT
12 3 $3.41
Table Adj_Reversal
ID LINE_NUM DOLLAR_AMT
12 3 ($3.41)
Table Adj_Retro
ID LINE_NUM DOLLAR_AMT
12 3 $0.00
12 3 $1.90
12 3 $1.42
12 3 $0.09
The following sql gives me the results below.
SELECT orig.ID AS ID1,
orig.LINE_NUM AS ID1_LINE,
reversal.ID AS ID2,
reversal.LINE_NUM AS ID2_LINE,
retro.ID AS ID3,
retro.LINE_NUM AS ID3_LINE
FROM schema.Adj_Orig orig
LEFT OUTER JOIN schema.Adj_Reversal reversal ON (reversal.ID = orig.ID
AND reversal.LINE_NUM = orig.LINE_NUM)
LEFT OUTER JOIN schema.Adj_retro ON (retro.ID = orig.ID
AND retro.LINE_NUM = orig.LINE_NUM)
WHERE orig.ID = '12'
ID1 ID1_LINE ID2 ID2_LINE ID3 ID3_LINE ORIG_AMT REVERSAL_AMT RETRO_AMT
12 3 12 3 12 3 $3.41 ($3.41) $0.00
12 3 12 3 12 3 $3.41 ($3.41) $1.90
12 3 12 3 12 3 $3.41 ($3.41) $1.42
12 3 12 3 12 3 $3.41 ($3.41) $0.09
I have this sql with sum functions giving the me the results below.
SELECT
orig.ID AS ID1,
orig.LINE_NUM AS ID1_LINE,
reversal.ID AS ID2,
reversal.LINE_NUM AS ID2_LINE,
retro.ID AS ID3,
retro.LINE_NUM AS ID3_LINE,
SUM(orig.DOLLAR_AMT) AS ORIG_AMT,
SUM(reversal.DOLLAR_AMT) AS REVERSAL_AMT,
SUM(retro.DOLLAR_AMT) AS RETRO_AMT -- Ok
FROM schema.Adj_Orig orig
LEFT OUTER JOIN schema.Adj_Reversal reversal ON (reversal.ID = orig.ID
AND reversal.LINE_NUM = orig.LINE_NUM)
LEFT OUTER JOIN schema.Adj_retro ON (retro.ID = orig.ID
AND retro.LINE_NUM = orig.LINE_NUM)
WHERE orig.ID = '12'
GROUP BY 1,2,3,4,5,6
;
ID1 ID1_LINE ID2 ID2_LINE ID3 ID3_LINE ORIG_AMT REVERSAL_AMT RETRO_AMT
12 3 12 3 12 3 $13.64 ($13.64) $3.41
I need the sql to give me the following results.
ID1 ID1_LINE ID2 ID2_LINE ID3 ID3_LINE ORIG_AMT REVERSAL_AMT RETRO_AMT
12 3 12 3 12 3 $3.41 ($3.41) $3.41
How do I get the ORIG_AMT & REVERSAL_AMT to only count the 3.41 value once.
Thansk!
I tried OVER (PARTITION BY), but it is giving me the same value.
Just remove the SUM() from orig_amt and reversal_amt columns:
SELECT
orig.ID AS ID1,
orig.LINE_NUM AS ID1_LINE,
reversal.ID AS ID2,
reversal.LINE_NUM AS ID2_LINE,
retro.ID AS ID3,
retro.LINE_NUM AS ID3_LINE,
orig.DOLLAR_AMT AS ORIG_AMT,
reversal.DOLLAR_AMT AS REVERSAL_AMT,
SUM(retro.DOLLAR_AMT) AS RETRO_AMT -- Ok
FROM schema.Adj_Orig orig
LEFT OUTER JOIN schema.Adj_Reversal reversal ON (reversal.ID = orig.ID
AND reversal.LINE_NUM = orig.LINE_NUM)
LEFT OUTER JOIN schema.Adj_retro ON (retro.ID = orig.ID
AND retro.LINE_NUM = orig.LINE_NUM)
WHERE orig.ID = '12'
GROUP BY 1,2,3,4,5,6,7,8
To your comment: just toss this in a subquery and sum once again:
SELECT ID1, ID1_LINE, ID2, ID2_LINE, ID3, ID3_LINE, SUM(ORIG_AMT) as ORIG_AMT, SUM(REVERSAL_AMT) as REVERSAL_AMT, SUM(RETRO_AMT) as RETRO_AMT
FROM
(
SELECT
orig.ID AS ID1,
orig.LINE_NUM AS ID1_LINE,
reversal.ID AS ID2,
reversal.LINE_NUM AS ID2_LINE,
retro.ID AS ID3,
retro.LINE_NUM AS ID3_LINE,
orig.DOLLAR_AMT AS ORIG_AMT,
reversal.DOLLAR_AMT AS REVERSAL_AMT,
SUM(retro.DOLLAR_AMT) AS RETRO_AMT -- Ok
FROM schema.Adj_Orig orig
LEFT OUTER JOIN schema.Adj_Reversal reversal ON (reversal.ID = orig.ID
AND reversal.LINE_NUM = orig.LINE_NUM)
LEFT OUTER JOIN schema.Adj_retro ON (retro.ID = orig.ID
AND retro.LINE_NUM = orig.LINE_NUM)
WHERE orig.ID = '12'
GROUP BY 1,2,3,4,5,6,7,8
) dt
GROUP BY 1,2,3,4,5