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)>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.

Answer 1

I got this error because of a silly mistake - I had my GROUP BY column in quotes!

Wrong

GROUP BY 'business'

Correct

GROUP BY business

Credit: @SOund's answer led me to figure this out

Answer 2

I had this error message when i had a "fixed" value column in SELECT and as a good boy, i used it in the GROUP BY too.

For example:

SELECT ID, SUM(Quantity), 'Person'
FROM Persons
GROUP BY ID, 'Person'

the fix:

SELECT ID, SUM(Quantity), 'Person'
FROM Persons
GROUP BY ID

The fixed 'Person' value is pointless since it's the same value throughout every row and there is nothing to group by with this value. Or this is my interpretation of the error.

The reason why i use this fixed value column, is because i was preparing queries to use it in a Pivot table.

Answer 3

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 ;)

Answer 4

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) > 2
)

Answer 5

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)>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

Answer 6

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)>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...

Answer 7

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)>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

Answer 8

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)>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.

Answer 9

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.