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

1

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:

pic1
pic11374×355 35.1 KB

This is my query:

pic2
pic2893×819 73.3 KB

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.

Thanks,
Sajeel

2

You also need to add EmailName in group by.

3

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.

example:

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
4

Thanks Jeff! That worked