Posted by rola on 02/15/06 19:09

Hi Group!

I am having a problem of using SUM under UPDATE statement. I understand
that SQL does not allow me to use SUM in UPDATE, but unfortunately, I
can not find a way to get around it. Can someone please give me some
idea? Thanks a million!

--Here is the criteria:
Under the same PORTFOLIO_ID, if the ACCOUNT_OPENDATE is in the same
month as the PORTFOLIO_OPENDATE, then sum the account_openamt for the
same PRODUCT, and update the CHECKING_OPENAMT, SAVINGS_OPENAMT,
CD_OPENAMT, etc in table PORTFOLIO. For other accounts opened NOT in
the same month as the PORTFOLIO_OPENDATE, just ignore them.

--Here are the tables
create table portfolio
(portfolio_id int
,portfolio_opendate smalldatetime
,checking_openamt money
,savings_openamt money
,cd_openamt money)

insert into portfolio values(1,'2/15/2005',0,0,0)

create table account
(portfolio_id int
,product varchar(20)
,account_opendate smalldatetime
,account_openamt money)

insert into account values(1,'checking','2/15/2005',2000)
insert into account values(1,'checking','2/20/2005',3000)
insert into account values(1,'savings','2/20/2005',3000)
insert into account values(1,'cd','5/15/2005',5000)

--Ideal Output--
id portfolio_opendate checking_openamt savings_openamt cd_openamt
1 2/15/2005 5000 3000 0

--Here is my query:
update portfolio
set checking_openamt=sum(b.account_openamt) --problem appears!
from portfolio as a
join account as b
on a.id=b.id
and year(a.portfolio_opendate)=year(b.account_opendate)
and month(a.portfolio_opendate)=month(b.account_opendate)
and product ='checking'
--and product='savings'
--and product='cd'

Thanks again!!

• Next in forum: SQL Server float data type
• Prev in forum: Re: very slow query (select count(*) from table)
• Thread view: Query Help with Update statement, thanks!

[Reply to this message]