SQLTeam.com | Weblogs | Forums

Query is giving an error because of CASE statement in the SELECT clause

Hi, I'm using a CASE statement in the SELECT clause of my query, and I'm getting the following error when I try to execute it:
Msg 8120, Level 16, State 1, Line 95
Column 'BLINDS_EDW.dbo.VW_EXACTTARGET_JOB_MASTER.EmailName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This is a pic of the error:

This is my query:

I added the column name, "EmailType" into the group by clause and I'm still getting the error. Any help is resolving this error would be greatly appreciated.


You also need to add EmailName in group by.

The group by won't know what 'EmailType' is - so it cannot group by that value. You would either need to restate the case expression in the group by, or use a CTE/derived table - query from that derived table and group.


with detailData
As (
Select ...
     , EmailType = CASE ...
     , {columns to be aggregated}
From ...
join ...
left join ...
Where ...
Select col1
     , col2
     , EmailType
     , {aggregate columns}
From detailData     dd
Group By
    , dd.col1
    , dd.col2
    , dd.EmailType;
1 Like

Thanks Jeff! That worked