Each group by expression must contain at least one column that is not an outer reference - On Turk colition

Question

I get this error

each group by expression must contain at least one column that is not an outer reference

I am inserting date into a temp table, and error is at the following group by query(only on some collation)

insert into @Temp(Name, ID)
   select 
      USR.Name,
      ISNULL((SELECT SUM(BCR.QUANTITY) 
              FROM Rate BCR            
              WHERE BCR.RateID = USR.RateID 
              GROUP BY USR.RateID), 0) AS TotalQuantity
   from 
      dbo.User as USR
   where 
      USR.Status = 1

Answer 1

You have a correlated sub query where USR.RateID is from the outer query. Since you are using USR.RateID in the where clause you can remove the GROUP BY clause since there will only ever be one value for USR.RateID for each invocation of the sub query.

insert into @Temp(Name, ID)
   select 
      USR.Name,
      ISNULL((SELECT SUM(BCR.QUANTITY) 
              FROM Rate BCR            
              WHERE BCR.RateID = USR.RateID), 0) AS TotalQuantity
   from 
      dbo.User as USR
   where 
      USR.Status = 1