Help using sum in an aggregate function

[Minimorgie]

Hi,

I am trying to update a table from two other tables and I am having trouble with the second sum where the total produced is null, it makes the total for the field concerned (april_actual) null even though the first part of the sum has inserted a value. I've tried using the isnull command but this doesn't help. The SQL following is what I am using

update part2_totals
set april_actual =(select sum(firm_units) from part2_mfrent where firm_handover between
'01-apr-2004' and '30-apr-2004') +
(select sum(firm_units) from part2_mfsale where firm_handover between
'01-apr-2004' and '30-apr-2004')

Any help would be appreciated.

Minimorgie

 

[vongrunt]

Can you show us SQL code with isnull() function?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[JamesLean]

update part2_totals
set april_actual = (select [b]ISNULL(sum(firm_units), 0)[/b] from part2_mfrent where firm_handover between
'01-apr-2004' and '30-apr-2004') +
(select [b]ISNULL(sum(firm_units), 0)[/b] from part2_mfsale where firm_handover between
'01-apr-2004' and '30-apr-2004')

--James

 

[donutman]

That's not the best way to warehouse your data...it will eventually get out of control. Part2_Totals should contain a MonthYear column and a UnitTotal column. The MonthYear column should be either an integer field (Year*100+Month) or a datatime field (30-apr-2004).
I assume that the firm_handover doesn't contain a time of day otherwise you shouldn't use Between.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[checkai]

update part2_totals
set april_actual = isnull((select Sum(ISNULL(firm_units, 0)) from part2_mfrent where firm_handover between
'01-apr-2004' and '30-apr-2004'),0) +
isnull((select sum(ISNULL(firm_units,0)) from part2_mfsale where firm_handover between
'01-apr-2004' and '30-apr-2004'),0)