The group query used in SQL Server is an order by clause. The order by clause must be used together with the aggregate function to complete the group query, in the SELECT query field, if the field does not use the aggregate function, it must appear in the order by clause (that is, the field name after the SELECT statement must either appear in the aggregate function, or use it in the order by clause)
You can also use the having clause to define query conditions in group queries. For example:
Select
Stuid as student ID, courseid as test number, AVG (score) as test average score
From
Score
Group
By stuid, courseid -- group by student ID and test number
Having
Count (score)> 1 -- the condition is that the number of score records after grouping is greater
The differences between where, group by, and having are as follows:
Where clause: remove data that does not meet the search criteria from the data source
Group by clause: group. Statistical functions (Aggregate functions) are used to calculate statistical values for each group.
Having clause: remove non-conforming data rows from each group in a group.