is not valid in the select list because the column is neither included in the aggregate function nor included in the GROUP by clause

is not valid in the select list because the column is neither included in the aggregate function nor included in the GROUP by clause

Suddenly see this problem, the head of a blindfolded, do not know what the meaning, and then think about, trying to select the options in the back, the problem of natural solution!The following is the newspaper "OrderDate Select Shipcountry,sum (ShipVia) as totalvia,orderdate as thefirsttime from orders group by ShipCountry, Corresponding from the Internet to see other friends also have this problem

For example, to display au_fname,au_lname,zip in the authors table, city,state information, and group by city (the same city is arranged together)

The wrong expression:
Select Au_fname,au_lname,zip, City,state
From authors
Group BY City

will appear: server: Msg 8120, Level 16, State 1, line 1
Column ' Authors.au_fname ' is not valid in the select list because the column is neither included in the aggregate function nor included in the GROUP by clause.
Server: Msg 8120, Level 16, State 1, line 1
Column ' authors.au_lname ' is not valid in the select list because the column is neither included in the aggregate function nor included in the GROUP by clause.
...... The error prompt.

The right expression:
Select Au_fname,au_lname,zip, City,state
From authors
GROUP BY City,au_lname,au_fname,zip,state

That is, when you specify group BY, all columns in the selection list that are not in an aggregate expression should be included in the group by list, or the group by expression must exactly match the Select-list expression.

In fact, there are better ways:
Select Au_fname,au_lname,zip, City,state
From authors
Order BY City
Even with the ORDER BY clause

or the authors table, which requires an author in each city to display information about the author:
Can be expressed as follows:
SELECT * FROM Authors
where au_id in
(select min (au_id)
From authors
Group by city)
In fact, the smallest ID is taken after grouping.

For example, to display au_fname,au_lname,zip in the authors table, city,state information, and group by city (the same city is arranged together)

The wrong expression:
Select Au_fname,au_lname,zip, City,state
From authors
Group BY City

will appear: server: Msg 8120, Level 16, State 1, line 1
Column ' Authors.au_fname ' is not valid in the select list because the column is neither included in the aggregate function nor included in the GROUP by clause.
Server: Msg 8120, Level 16, State 1, line 1
Column ' authors.au_lname ' is not valid in the select list because the column is neither included in the aggregate function nor included in the GROUP by clause.
...... The error prompt.

The right expression:
Select Au_fname,au_lname,zip, City,state
From authors
GROUP BY City,au_lname,au_fname,zip,state

That is, when you specify group BY, all columns in the selection list that are not in an aggregate expression should be included in the group by list, or the group by expression must exactly match the Select-list expression.

In fact, there are better ways:
Select Au_fname,au_lname,zip, City,state
From authors
Order BY City
Even with the ORDER BY clause

or the authors table, which requires an author in each city to display information about the author:
Can be expressed as follows:
SELECT * FROM Authors
where au_id in
(select min (au_id)
From authors
Group by city)
is actually the line that takes the smallest ID after grouping

is not valid in the select list because the column is neither included in the aggregate function nor included in the GROUP by clause