>>Hello -
>>
>>I am trying to write a SQL select. My goal should be obvious from my code.

>>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?
>>Thanks
>
>Try:
>
>

>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 RTAid

>
>See this blog post
>http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/aggregates-with-multiple-tables


Thanks Naomi. I have to do some validation but it looks like that will work well.