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

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

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

Code:

---

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):-

Code:

---

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

---

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:

Code:

---

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

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.

Probably this may help:

Code:

---

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

---

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

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:afrog:

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

Please mark your thread as Resolved.

Quote:

---

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:)

Quote:

---

Originally Posted by FunkyDexter

...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):

Code:

---

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?

Quote:

---

How is it cartesian?

---

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

Code:

---

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:rolleyes:

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