Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

sql group by not contained in either an aggregate function or the group by clause
sql error not in aggregate function or group by clause
in select clause is neither an aggregate nor in the group by clause
column is not contained in the entity selection list
group by invalid in select list
analysisexception cannot combine select distinct with aggregate functions or group by
reference to a column in the aggregation context may not appear outside of an aggregate function
group by invalid column name

This question already has answers here:

Suppose I have the following table T:

a   b
--------
1   abc
1   def
1   ghi
2   jkl
2   mno
2   pqr

And I do the following query:

SELECT a, b
FROM T
GROUP BY a

The output should have two rows, one row where a=1 and a second row where a=2.

But what should the value of b show on each of these two rows? There are three possibilities in each case, and nothing in the query makes it clear which value to choose for b in each group. It's ambiguous.

This demonstrates the single-value rule, which prohibits the undefined results you get when you run a GROUP BY query, and you include any columns in the select-list that are neither part of the grouping criteria, nor appear in aggregate functions (SUM, MIN, MAX, etc.).

Fixing it might look like this:

SELECT a, MAX(b) AS x
FROM T
GROUP BY a

Now it's clear that you want the following result:

a   x
--------
1   ghi
2   pqr

How to fix the Error 'Column is invalid in the select list because it is , The error “Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause” mentioned below arises when you execute “GROUP BY” query, and you have included at least one column in the select list that is neither part of the group by clause nor it is contained in an “Msg 8120, Level 16, State 1, Line 16 Column ‘people.age’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”.

Your query will work in MYSQL if you set to disable ONLY_FULL_GROUP_BY server mode (and by default It is). But in this case, you are using different RDBMS. So to make your query work, add all non-aggregated columns to your GROUP BY clause, eg

SELECT col1, col2, SUM(col3) totalSUM
FROM tableName
GROUP BY col1, col2

Non-Aggregated columns means the column is not pass into aggregated functions like SUM, MAX, COUNT, etc..

SQL GROUP By and the "Column 'name' is invalid in the select list , I get an error "Column 'name' is invalid in the select list because t is not contained in either an aggregate function or the GROUP BY clause."? Employee First Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause Saying that, there are other columns that need attention too. Either reduce the columns returned to only those needed or include the columns in your GROUP BY clause or add aggregate functions (MIN/MAX).

"All I want to do is join the tables and then group all the employees in a particular location together."

It sounds like what you want is for the output of the SQL statement to list every employee in the company, but first all the people in the Anaheim office, then the people in the Buffalo office, then the people in the Cleveland office (A, B, C, get it, obviously I don't know what locations you have).

In that case, lose the GROUP BY statement. All you need is ORDER BY loc.LocationID

Tutorial: Advanced SELECTs | SQL Tutorial, Is invalid in the select list because it is not contained group by? Column 'ACCOUNT.ACCOUNT_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause 1 Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause (Nested SELECT, inner join)

Basically, what this error is saying is that if you are going to use the GROUP BY clause, then your result is going to be a relation/table with a row for each group, so in your SELECT statement you can only "select" the column that you are grouping by and use aggregate functions on that column because the other columns will not appear in the resulting table.

GROUP BY and FILTER, where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning. Column 'fid_crm_sp_recon.URL' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. SELECT URL, Name, statusCode, ErrorType FROM fid_crm_sp_recon GROUP BY ErrorType

Aggregate functions in SQL, Column is invalid in the select list because it is not contained in either an aggregate function or You're getting the same object_id multiple times because you get a select I.object_id ,S.name ,si.name ,I.last_user_scan ,i.user_scans S ON I.object_id = S.object_id WHERE type = 'U' group by S.name;. Msg 8120, Level 16, State 1, Line 1 Column 'Transactions.TimeStamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Why? Think about it for a moment, and it's pretty obvious: Which TimeStamp value do you want SQL to use?

Column is invalid in the select list because it is not contained in , Column 'people. age' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. In other words, the computer has the same restriction we do -- it cannot answer that question unless you are more specific! Column 'CM.PfmFolder.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause 1 Column is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause

But WHY Must That Column Be Contained in an Aggregate Function , Hibernate throws "Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" - SQL Server · Hibernate ORM · learner November 23, 2018, 10:12am #1. i am facing problem when i Server: Msg 8120, Level 16, State 1, Line 1 Column 'xyz' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Comments

When you use group by you have to select the thing specifically that you are grouping
You are probably confusing GROUP BY with ORDER BY. Grouping is used to aggregate columns; ordering is sorting the result.
Bill - an aside - you said that its not "clear which value to choose for b in each group". Why doesn't SQL put b1,b2,b3 all together in that column ?
@davidblaine, MySQL has a function GROUP_CONCAT() for that. dev.mysql.com/doc/refman/5.5/en/… But in standard SQL, each column should contain only one value. That's fundamental to relational theory too.
Love it when folks take the time to explain using first principles. Excellent explanation Bill. Thanks.
I think this is negative point of MSSQL because when we have some LEFT JOINs and group by the PK of the right table then reasonably we must be able to get the columns of the right table easily without ANY AMBIGUOUS situation (MySQL handles this).
Also AFAIK MsSQL had some trick like GROUP_CONCAT too... hmm... something like STUFF(( SELECT (' - '+ColName) AS ColName .... FOR XML PATH('') ), 1, 3, '').
this works like a charm with multiple inner joins.
Thats not what i meant. order by will put all employees in the same city one after the other. It will do this for all the cities. I needed to know how many employees in a city instead of which employees in a city. makes sense ?
then replace the first line of your original example with: SELECT loc.LocationID, COUNT(*)
yeah, thats what i wanted. Thanks. Looks like i did not frame my question properly. But, Bill Karwin's answer made me understand the reason behind the error. So I accepted his. Thanks again.