>select b.RTAElement, a.tipid, rtaid, a.projectname, sponsor, developmentstatus, SUM(c.amount) as RTACommitted, SUM(d.amount) as RTASpent >from TIP_ProjectS a >inner join RTA_GeneralInfo b on a.TipID = b.tipid >left join TIP_ApprovedFunding c on a.TipID = c.tipid and c.FundType = 'RTA' >left join TIP_Ledger d on a.TipID = d.tipid and d.FundType = 'RTA' >group by b.RTAElement, a.tipid, rtaid, a.projectname, sponsor, developmentstatus >order by RTAid >>At present, it is not correct as I want the 2 sum amounts to only include FundTypes 'RTA'. Can someone help me filter the sums but not drop any records where the sum might be null or zero?
select b.RTAElement, a.tipid, rtaid, a.projectname, sponsor, developmentstatus, c.RTACommitted, d.RTASpent from TIP_ProjectS a inner join RTA_GeneralInfo b on a.TipID = b.tipid left join (select TipId, SUM(amount) as RTACommited FROM TIP_ApprovedFunding WHERE FundType = 'RTA' GROUP BY TipId) c on a.TipID = c.tipid left join (SELECT TipId, SUM(Amount) AS RTASpent FROM TIP_Ledger WHERE FundType = 'RTA' GROUP BY TipID) d ON d.TipID = a.TipId order by RTAidSee this blog post