Each GROUP BY expression must contain at least one

问题:

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.

回答1:

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

回答2:

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

回答3:

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.

回答4:

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.

回答5:

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

回答6:

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
)

回答7:

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