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

Question

What am I doing wrong here  I am getting this error on   SELECT LEFT SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000                PATINDEX     0-9     SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9                  batchinfo datapath   8000  -1               qvalues name              qvalues compound              qvalues rid FROM batchinfo JOIN qvalues ON batchinfo rowid qvalues rowid WHERE LEN datapath  gt 4 GROUP BY 1 2 3 HAVING rid  MAX rid    I would like to group by the first  second  and third columns having the max rid   It works fine without the group by and having

User · Answer

Well  as it was said before  you can t GROUP by literals  I think that you are confused cause you can ORDER by 1  2  3  When you use functions as your columns  you need to GROUP by the same expression  Besides  the HAVING clause is wrong  you can only use what is in the agreggations  In this case  your query should be like this   SELECT  LEFT SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000   PATINDEX     0-9     SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000  -1   qvalues name  qvalues compound  MAX qvalues rid  MaxRid FROM batchinfo join qvalues  ON batchinfo rowid qvalues rowid WHERE LEN datapath  gt 4 GROUP BY  LEFT SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000   PATINDEX     0-9     SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000  -1   qvalues name  qvalues compound

User · Answer

To start with you can t do this   having rid  MAX rid    The HAVING clause can only contain things which are attributes of the aggregate groups   In addition  1  2  3 is not valid in GROUP BY in SQL Server - I think that s only valid in ORDER BY   Can you explain why this isn t what you are looking for   select  LEFT SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000   PATINDEX     0-9     SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000  -1   qvalues name  qvalues compound  MAX qvalues rid   from batchinfo join qvalues on batchinfo rowid qvalues rowid where LEN datapath  gt 4 group by LEFT SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000   PATINDEX     0-9     SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000  -1   qvalues name  qvalues compound

User · Answer

I think you re not using GROUP BY properly   The point of GROUP BY is to organize your table into sections based off a certain column or columns before performing math aggregate functions   For example  in this table   Name    Age   Salary Bob     25     20000 Sally   42     40000 John    42     90000   A SELECT statement could GROUP BY name  Bob  Sally  and John would each be separate groups   Age  Bob would be one group  Sally and John would be another   or Salary  pretty much same result as name    Grouping by  1  doesn t make any sense because  1  is not a column name

User · Answer

When you re using GROUP BY  you need to also use aggregate functions for the columns not inside your group by clause   I don t know exactly what you re trying to do  but I guess this would work   select      LEFT SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000       PATINDEX     0-9     SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000  -1       qvalues name      qvalues compound      MAX qvalues rid  from     batchinfo join qvalues on batchinfo rowid qvalues rowid where     LEN datapath  gt 4 group by     LEFT SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000       PATINDEX     0-9     SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000  -1       qvalues name      qvalues compound having     rid  MAX rid    Edit  What I m trying to do here is a group by with all fields but rid  If that s not what you want  what you need to do in order to have a valid SQL statement is adding an aggregate function call for each removed group by field

User · Answer

Here s a simple query to find company name who has a medicine type of A and makes more than 2   SELECT CNAME  FROM COMPANY  WHERE CNO IN       SELECT CNO      FROM MEDICINE      WHERE type  A       GROUP BY CNO HAVING COUNT type   gt  2

User · Answer

You can t group by literals  only columns   You are probably looking for something like this   select  LEFT SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000   PATINDEX     0-9     SUBSTRING batchinfo datapath  PATINDEX    0-9  0-9  0-9     batchinfo datapath   8000  -1  as pathinfo  qvalues name  qvalues compound  qvalues rid  from batchinfo join qvalues on batchinfo rowid qvalues rowid where LEN datapath  gt 4 group by pathinfo  qvalues name  qvalues compound having rid  MAX rid    First of all  you have to give that first expression a column name with as  Then you have to specify the names of the columns in the group by expression

User · Answer

I just found this error   while using GETDATE    i e outer reference  in the group by clause in a select query    When replaced it with date column from the respective table it cleared    Thought to share as a simple example  cheers

[sql-server] Each GROUP BY expression must contain at least one column that is not an outer reference

Examples related to sql-server

Examples related to tsql