[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

[RhinoBull]

NOt sure if this syntax is valid in sql server (I think you will need to use Case statement) but in Oracle you can do something like this:

SELECT
    sum(decode(t1.a, NULL, t2.b, t1.a))
FROM
    table1 t1,
    (SELECT id, a, b FROM table1
     WHERE id = 123
     AND a IS NOT NULL) t2
WHERE
    t1.id = 123

... so give it the try ...

[FunkyDexter]

Hi Rhino, thanks for the response

I'm trying to get my head round what you're doing there and I'm not quite sure I understand.

decode(t1.a, NULL, t2.b, t1.a) <- That's the equlivalent of isnull(t1.a, t2.b) right? ie use the value t1.a unless it's null, in which case use t2.b

And I think you're then selecting from a cartesian product of t1 and the product of the inner select. Should the inner select actually be selecting from table2 where it's foreign key matches table1's primary key?

I can see a potential problem because the cartesian product would result in the rows in table1 being repeated so, if I had three corresponding rows in table2 and a values in table1.a, the sum would give me a result of 3 times table1.a. Does the "a IS NOT NULL" somehow eliminate that? (I tried structuring my original query with a left join to the clockins table (table2 in this scenario) but I noticed I got the problem with repetition of table1 rows)

Thanks for your help so far.

[anhn]

Probably this may help:

SELECT SUM(ISNULL(S.F1, S.F22)) 
FROM (SELECT T1.F1, SUM(T2.F2) AS F22 
FROM T1 LEFT JOIN T2 ON T1.ID = T2.ID 
GROUP BY T1.ID, T1.F1) AS S

[FunkyDexter]

Ooh, now that looks like it should work. I'll have a punt.

[FunkyDexter]

Well that parses and is giving me reasonable looking figures. I need to do some double checking but I think that's cracked it. Thanks

[anhn]

What do you mean "reasonable looking"? Are they not perfectly correct?
It can only right or wrong, nothing in the middle.

Please mark your thread as Resolved.

[FunkyDexter]

What do you mean "reasonable looking"?

I mean they look to be about what I'd expect for my dataset but I haven't sat down and actually added up all the numbers from the data to be certain yet. I've just done a quick bit of spot checking and it's right so far but I want to do some more checking before I'm satisfied enough to stick it in front of the boss. It's looking good though

[RhinoBull]

...I can see a potential problem because the cartesian product would result in the rows in table1 being repeated so....

How is it cartesian? You self joining table on itself but also eliminating some unnecessary records from subquery.
And the way DECODE works is basically like If-Else-End If construct so in your case it would be (you should disregard the syntax of course):

decode(t1.a, NULL, t2.b, t1.a)

If t1.a = Null Then
    'return t2.b
Else
    'return t1.a
End If

Wasn't it what you asled in your op?

[FunkyDexter]

How is it cartesian?

It may not be but I was unclear on what you were doing. Certainly this:-

Select *
From Table1, Table2

would create a cartesian because there's no joining clause between Table1 and Table2. I'd expect a similar effect if one of the tables was actually a subquery but I could well be missing something. To be honest, I was struggling to work out what your query did and I probably haven't understood it correctly.

I understand what decode does now. In this scenario it serves the same purpose as isnull. Thanks for the clarification on that.

I think I may have been unclear in my OP. I was happy with the isnull. The problem I was facing was that I wanted to do an aggregate function (sum)across a field in a parent table. But, if that field contained a null, I wanted to calculate the replacement by doing an aggregate function across a child table (nb not a join back to the same table). From the error message sql server gave me I'm guessing it doesn't support aggregates of aggregates - not sure why because it seems logically definable to me. I think this explanation probably doesn't make what I was after any clearer but it's damn hard to describe I'm afraid

Anyway, anhn's idea has definitely solved the issue so thanks all round