FIX: Error "Column '<column name>' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" when you run a query in SQL Server 2012

Article translations Article translations
Article ID: 2873474 - View products that this article applies to.
Expand all | Collapse all

On This Page

Symptoms

Consider the following scenario:
  • You have a computer that has Microsoft SQL Server 2012 installed. 
  • You have a query that contains a CASE statement in a SELECT statement and a CASE statement in a GROUP BY statement.
  • The two CASE statements evaluate to NULL.
  • You run the query.
In this scenario, you receive the following error message:
Msg 8120, Level 16, State 1, Line 3

Column '<column name>' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
For example, you run the following query:
create database test
go
use test
go
create table tvt ( v1t binary(1) null)
create table tln (ln numeric(5) not null)
go
select
case 
when 1=2 then t1.col1
else 10
end,
case
when t1.col1 = 2 then t1.col2
end
from (
select t2.v1t as col1, 10 as col2 
from tvt t2
) t1
group by 
case 
when 1=2 then t1.col1
else 10
end,
case
when t1.col1 = 2 then t1.col2
end
go
In the query, the statement "when 1=2 then t1.col1 else 10" evaluates to 10. When you replace "10" by using NULL, this issue occurs.
Note This issue does not occur when you remove either of the CASE statements from the SELECT statement or from the GROUP BY statement.

Resolution

After you apply the fix, you have to enable a trace flag to make the hotfix work. Please contact Microsoft support when you need to apply this fix. The issue was first fixed in the following cumulative update of SQL Server.

Cumulative Update 6 for SQL Server 2012 SP1

About cumulative updates for SQL Server

Collapse this imageExpand this image
assets folding start collapsed
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
Collapse this imageExpand this image
assets folding end collapsed

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Properties

Article ID: 2873474 - Last Review: September 16, 2013 - Revision: 2.0
Applies to
  • Microsoft SQL Server 2012 Service Pack 1, when used with:
    • Microsoft SQL Server 2012 Developer
    • Microsoft SQL Server 2012 Enterprise
    • Microsoft SQL Server 2012 Standard
    • Microsoft SQL Server 2012 Web
Keywords: 
kbqfe kbfix kbsurveynew kbexpertiseadvanced KB2873474

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com