The column in the selected list is invalid because it is not included in the aggregate function or group by clause.

 

 

T-SQL Core Statement Form:

Select -- specify the columns or rows to be selected and their limits
[Into] -- into clause, specifying that the result is saved to the new table
From -- from clause, specifying a table or view
[Where] -- where clause, specifying query Conditions
[Group by] -- group by clause, specifying the grouping expression
[Having] -- having clause, specifying grouping statistical conditions
[Order by [ASC | DESC] -- ORDER clause, specifying the sort expression and order

Aggregate functions:

Count: calculates the number of items in the Group and returns an integer of the int type.

Group by clause: Specifies the group used to place output rows. When group by is specified, all columns in any non-aggregate expression in the selection list should be included in the group by list, or the group by expression must exactly match the expression in the selection list. If the select clause contains an aggregate function, the aggregate value of each group is calculated.

Example 1 (an aggregate function exists in the select list ):

Incorrect syntax:

Select u_id, house_addrinfo, count (u_id) as CX
From yx_chushou
Group by u_id
Order by CX DESC

This statement shows that "the column 'yx _ chushou. house_addrinfo' in the selection list is invalid because the column is not included in the aggregate function or group by clause. "Error prompt.

Correct syntax:

Select u_id, min (house_addrinfo), count (u_id) as CX
From yx_chushou
Group by u_id
Order by CX DESC
Or

Select u_id, house_addrinfo, count (u_id) ascx
From yx_chushou
Group by u_id, house_addrinfo
Order by CX DESC

 
Example 2 ):

Incorrect syntax:
Select au_fname, au_lname, zip, city, state
From authors
Group by city

Server: Message 8120, level 16, status 1, Row 1
The 'authors. au_fname' column is invalid in the selection list because it is neither included in the aggregate function nor in the group by clause.
Server: Message 8120, level 16, status 1, Row 1
The 'authors. au_lname' column is invalid in the selection list because it is neither included in the aggregate function nor in the group by clause.
 
Correct syntax:
Select au_fname, au_lname, zip, city, state
From authors
Group by city, au_lname, au_fname, zip, state

Or:
Select au_fname, au_lname, zip, city, state
From authors
Order by city --- use the order by clause for sorting

That is, when group by is specified, all columns in any non-aggregate expression in the selection list should be included in the group by list, or the group by expression must exactly match the expression in the selection list.

 

 

 

Small White: because the result is a table, it is generally not written in select *, sum (job_id), or sum is a value. Who do you want it to show a row in parallel?

 

If select job_id, job_desc, sum (max_lvl) from jobs group byjobs. job_id, job_desc

To show vertices in parallel, the corresponding columns should be written in group by to ensure that a value appears once and a value corresponds to a sum.

 

Add a column to group.

The column in the selected list is invalid because it is not included in the aggregate function or group by clause.