Each GROUP BY expression must contain at least one column that is not

• Yusuf Bhiwandiwala

  Ten Centuries

  Points: 1298

  More actions

  January 21, 2007 at 8:26 am

  #175536

  hi guys,

  We have just replaced SQL 2000 with SQL 2005.

  The following type of statements are creating a problem in 2005 while

  they wer running fine in 2000.

  Consider the update statement:

  update  a

  set       a.col3 = (select   count(b.idcol)

                           from      tableB b with (nolock)

                           where    b.idcol = a.idcol

                           group by a.col2 

                          &nbsp

  from   tableA a with (nolock)

  As you see, the group by clause in the select query references only the

  column of the outer table tableA.

  This throws an error in SQL 2005 (not in SQL 2000) saying :

  "Msg 164, Level 15, State 1, Line 1

  Each GROUP BY expression must contain at least one column that is not

  an outer reference."

  Could not find any documentation about this error .

  In the above case, i gather,  my group by clause should also contain at

  least one column of the inner table tableB??

  How do i get rid of this error without losing my functionality and

  minimal code change, as of now!!!!

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  January 22, 2007 at 1:11 am

  #684613

  update  c

  set  c.col3 = d.qwerty

  from  tableA as c

  inner join (

     select  a.col2,

       count(b.idcol) as qwerty

     from  tableB as b

     inner join tableA as a on a.idcol = b.idcol

     group by a.col2

   &nbsp as d on c.col2 = d.col2

   

  ---

  N 56°04'39.16"
  E 12°55'05.25"