SQL server syntax error in update statement, but I can't see it

I'm getting a syntax error on this query, but I can't figure it out.

Incorrect syntax near the keyword 'group'.

I believe its on the last group by, but I don't see whats wrong. Can anyone suggest how to correct this?

UPDATE [NCLGS].[dbo].[CP_CustomerShipTo]
SET     TimesUsed = TimesUsed + B.NewCount
from [NCLGS].[dbo].[CP_CustomerShipTo] CST
INNER JOIN (    
    Select
        PKH.CompanyCode, 
        PKH.CompanyName, 
        PKH.Addr1, 
        PKH.Addr2, 
        PKH.City, 
        PKH.State, 
        PKH.Zip, 
        Count(recid) As NewCount
    from avanti_packingslipheader PKH 
    where pksdate > dbo.ufn_StartOfDay(DATEADD(d, -1, GETDATE() ) )   
    group by 
        PKH.CompanyCode, 
        PKH.CompanyName, 
        PKH.Addr1, 
        PKH.Addr2, 
        PKH.City, 
        PKH.State, 
        PKH.Zip 
) B 
ON CST.CustomerCode     =   B.CompanyCode
   AND CST.ShipToName       =   B.CompanyName
   AND CST.ShipToAddress1   =   B.Addr1
   AND CST.City             =   B.City
   AND CST.PostalCode       =   B.Zip

group by 
    PKH.CompanyCode, 
    PKH.CompanyName, 
    PKH.Addr1, 
    PKH.Addr2, 
    PKH.City, 
    PKH.State, 
    PKH.Zip

BACKGROUND - I'm trying to do an update statement with a Count(), but of course you can'开发者_如何学Ct use agg. functions in an update set statement, so I'm trying to use a subquery.

You have already got GROUP BY inside the subselect, so what does the outer GROUP BY stand for?

You can't reference an alias in a subselect from an outer GROUP BY. But in any event you can't use GROUP BY with an UPDATE statement, and that's what the error message is about.

Try removing the last Group By. What exactly are you hoping this last group by will do?

Change the code to this:

update mytable set
mycolumn = mycolumn + (select x from ...);