2 Aggregate (Sum) Queries and Union 1

[MCCooper]

I am using a Union statement to combine 2 aggregate (sum) select statements but I really want to sum the whole lot where the ParentID is the same number.

My statement as it stands is:

SELECT Ref_Code AS ParentID,
SUM(LMR_CurrentLoanBal) AS CurrentLoanBalance
FROM Loan_MasterReference
WHERE ISNULL(LMR_KeyIDLink,0) = 0
GROUP BY Ref_Code

UNION ALL

SELECT LMR_KeyIDLink AS ParentID,
SUM(LMR_CurrentLoanBal) AS CurrentLoanBalance
FROM Loan_MasterReference
WHERE ISNULL(LMR_KeyIDLink,0) <> 0
GROUP BY LMR_KeyIDLink
ORDER BY Ref_Code

Any help will be much appreciated.

Thanks

Matt

 

[swampBoogie]

[code]
select parentId,sum(CurrentLoanBalance)
from (
SELECT Ref_Code AS ParentID,
SUM(LMR_CurrentLoanBal) AS CurrentLoanBalance
FROM Loan_MasterReference
WHERE ISNULL(LMR_KeyIDLink,0) = 0
GROUP BY Ref_Code

UNION ALL

SELECT LMR_KeyIDLink ,
SUM(LMR_CurrentLoanBal) 
FROM Loan_MasterReference
WHERE ISNULL(LMR_KeyIDLink,0) <> 0
GROUP BY LMR_KeyIDLink
)
group by parentID
ORDER BY ParentID

 

[swampBoogie]

Forgot the correlation name for the derived table

select parentId,sum(CurrentLoanBalance)
from (
SELECT Ref_Code AS ParentID,
SUM(LMR_CurrentLoanBal) AS CurrentLoanBalance
FROM Loan_MasterReference
WHERE ISNULL(LMR_KeyIDLink,0) = 0
GROUP BY Ref_Code

UNION ALL

SELECT LMR_KeyIDLink ,
SUM(LMR_CurrentLoanBal)
FROM Loan_MasterReference
WHERE ISNULL(LMR_KeyIDLink,0) <> 0
GROUP BY LMR_KeyIDLink
) as dt
group by parentID
ORDER BY ParentID

 

[MCCooper]

Thanks swampBoogie. Second one worked well.

 

[ESquared]

for what it's worth, you don't need the ISNULL in there.

WHERE LMR_KeyIDLink <> 0

is exactly the same.

-------------------------------------
A sacrifice is harder when no one knows you've made it.