Using Aggregate in Update Statement 1

[bustercoder]

Hello, I'm trying to run this update (testing with the select statement first) with a group by and keep getting "An aggregate may not appear in the set list of an UPDATE statement":

An aggregate may not appear in the set list of an UPDATE statement:

update SY00500 set NUMOFTRX = count (SOP10100.SOPNUMBE), BCHTOTAL = sum(DOCAMNT)

--SELECT count (SOP10100.SOPNUMBE), BCHTOTAL = isnull(sum(DOCAMNT),0),SOP10100.BACHNUMB, COUNT(*) AS Expr1, SY00500.BCHSOURC, SY00500.NUMOFTRX, SY00500.BCHTOTAL, SY00500.BACHNUMB AS Expr2
FROM SOP10100 RIGHT OUTER JOIN
SY00500 ON SOP10100.BCHSOURC = SY00500.BCHSOURC AND SOP10100.BACHNUMB = SY00500.BACHNUMB
GROUP BY SOP10100.BACHNUMB, SY00500.BCHSOURC, SY00500.NUMOFTRX, SY00500.BCHTOTAL, SY00500.BACHNUMB
HAVING (SY00500.BCHSOURC = 'sales entry')

I've tried to nest the select and use an outer grouping, but I'm not having any success. Could someone please show me the best approach?

Thanks,
Buster

 

[gmmastros]

This should parse correctly, but it is up to you to make sure that the logic is correct. Since this is an update statement, you should make a backup of your database before running this.

[COLOR=blue]update[/color] SY00500 
[COLOR=blue]set[/color]    NUMOFTRX = A.CountNUMOFTRX, 
       BCHTOTAL = A.SumDOCAMT
[COLOR=blue]From[/color]   SY00500
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]SELECT[/color] [COLOR=#FF00FF]count[/color](SOP10100.SOPNUMBE) [COLOR=blue]As[/color] CountNUMOFTRX, 
                [COLOR=#FF00FF]isnull[/color](sum(DOCAMNT),0) [COLOR=blue]AS[/color] SumDOCAMT,
                SY00500.BCHSOURC, 
                SY00500.BACHNUMB
         [COLOR=blue]FROM[/color]   SOP10100 
                [COLOR=#FF00FF]RIGHT[/color] [COLOR=blue]OUTER[/color] [COLOR=blue]JOIN[/color] SY00500 
                  [COLOR=blue]ON[/color] SOP10100.BCHSOURC = SY00500.BCHSOURC 
                  AND SOP10100.BACHNUMB = SY00500.BACHNUMB
         [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] SOP10100.BACHNUMB, 
                SY00500.BCHSOURC, 
                SY00500.NUMOFTRX, 
                SY00500.BCHTOTAL, 
                SY00500.BACHNUMB
         [COLOR=blue]HAVING[/color] (SY00500.BCHSOURC = [COLOR=red]'sales entry'[/color]) 
         ) [COLOR=blue]As[/color] A
         [COLOR=blue]ON[/color] SOP00500.BCHSOURC = A.BCHSOURC 
         AND SOP00500.BACHNUMB = A.BACHNUMB

-George

"the screen with the little boxes in the window." - Moron

 

[bustercoder]

Thanks George! You guys are spoiling me! I get more help and learn more from you guys than the people I work with.
Buster

 

[gmmastros]

I'm glad I could help.

And please, realize that I like to help people because there was I time I needed help. I expect that there will come a time when you will be able to help others.

-George

"the screen with the little boxes in the window." - Moron