HOW TO FIX : Column is invalid in the select list because it is not contained in either an aggregate function or the group by clause.

Praveen Singh         No comments

You might had faced this problem several times and probably fixed it also. But do you know there is more than one way to fix this particular problem? So, in this post I will talk about why this problem occurs and what are the possible fixes for this problem.

My table structure:

Why this occurs?

 Each group is represented by only one result row; therefore, all expressions that appear in those clauses must guarantee a single result value per group. There’s no problem referring directly to elements that appear in the GROUP BY clause because each of those returns only one distinct value per group. But if you want to refer to elements from the underlying tables that don’t appear in the GROUP BY list, you must apply an aggregate function to them. That’s how you can be sure that the expression returns only one value per group.
Possible fixes:

First fix would be to put that specific column into the GROUP BY clause.

Apply an aggregate function on that column; like in this scenario I have applied MAX function. Because the values are getting grouped by CLASS and the City are same for both person belonging to a particular CLASS. This will work here. But this will not work in a situation where both persons belong  to different City. So, use it but with care…

This fix is my favorite one and I use it a lot in my work. I can take all the aggregate functions and group by things in a CTE (Common Table Expression) and use it to join the main table. In this way, I don’t have to worry about the putting columns under any kind of function or in any group by clause.But In this scenario we don't have any primary key to take join.

Let's say there is an id field in our table. Then we can try something like this :

Published by Praveen Singh

A blogger by passion.You can find me tucked in my bed and blogging on weekends when not roaming around. Besides blogging, I love music and you can find my songs on my fb page:PraveenUnplugged
Follow on Youtube : Videos On Latest Happenings |ThingsToKnow
Follow us Talend In Action

0 responses:

© 2015 Techie's House. Designed by Bloggertheme9