Invalid Use of group function

[bxgirl]

Using mysql 4.0

I get the following error message

"Invalid Use of group function"

with the following code:

update custRP c, tbl_acctbal a
set c.rpBal = sum(a.currRPBAL)
where c.custid = a.custid

Why?

 

[guelphdad]

In order to use a SUM clause you must group by the rows in that column or you can't use the SUM clause.

so add GROUP BY a.currRPBAL to the last line of your query.

 

[guelphdad]

actually you won't want to group by that field necessarily, you will need to figure out which column you need to group by.

 

[bxgirl]

Your suggestion didn't work.

This is the table I want to update

create temporary table custRP(
custid int,
rpBal int)

The update code:

update custRP c, tbl_acctbal a
set c.rpBal = sum(a.currRPBAL)
where c.custid = a.custid
group by custRP.custid

Adding the group by doesn't work. I get the following error message "you have an error in your sql syntax..."

 

[guelphdad]

When posting an error message it is helpful to post the entire message, they mean different things when you can see the entire message.

I can tell you why you are getting this specific error though, you can't refer to the table custRP in your group by clause since you have given your table an alias.

more importantly though, can you show some sample rows of both tables and it might clear up exactly what you are tryingto do?