CODEWITHSUNDEEP
sql-server
Iry
Iry

Reputation: 1

SQL error 164 on a group by query

I found a few examples of this error, but I can't seem to figure out how they relate to the query where I'm getting this error.

"Error: Each GROUP BY expression must contain at least one column that is not an outer reference. SQLState: S1000 ErrorCode: 164"

From what I can tell it happens when you try to group by a literal (I don't think that's the case) or when you try to group by without using a variable from the inner query. . .which also isn't the case here.

Any insights would be greatly appreciated. Thanks!

select Template_ID, sum(processed) as processed
from cache_table (nolock)
where Template_ID IN (208, 207)
and period between '2015-11-1' AND '2015-11-27'
group by 1
;

Upvotes: 0

Views: 1110

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270341

SQL Server doesn't understand positional arguments in group by. So, repeat the column name:

select Template_ID, sum(processed) as processed
from cache_table (nolock)
where Template_ID IN (208, 207) and
      period between '2015-11-01' AND '2015-11-27'
group by Template_ID;

The positional syntax is permitted for order by. In general, though, it is better to use column names in the order by clause as well.

Note: some databases (such as Postgres) do support positional arguments for group by.

Upvotes: 1

Related Questions