[RESOLVED] Can I do a sub select for the isnull substitution within an agregate function

[FunkyDexter]

...now that's a mouthful for a thread title.

What I'm trying to achieve is something like this:-

select sum(isnull(field1, (Select Sum(field2)
                                 from table2
                                 where table2.table1ID = table1.ID)))
From table1

Basically, I want to sum field1 from table 1. But for each row in table 1, if field 1 is null I want to sum field2 from it's child table, table2. This seems logically consistent to me but sqlserver gives an error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

So is there another way I can phrase the query to achieve the desired result.


Here's the actual query I'm trying to achieve (it may clarify the question a bit):-

select sum(isnull(ManagerAprovedMinutes, (select sum(datediff(mi, clockIn, clockOut)) 
                                                         from ClockIns 
                                                         where ClockInDayID = ClockInDays.ID)))
From tmUsers
Left Join ClockInDays
	on tmUsers.ID = UserID
	and datepart(week, ClockInDate) = 26
	and datepart(year, ClockInDate)  = 2008