Summary of Groupby, having, orderby, and Distinct under SqlServer

This article summarizes the usage of Groupby, having, orderby, and Distinct in SqlServer. If you need it, you can learn about it.

This article summarizes the usage of Group by, having, order by, and Distinct in SqlServer. If you need it, you can learn it.

Go straight to the topic and run the following SQL statement:

The Code is as follows:
Select count (*) as count, REQUEST, METHOD FROM REQUESTMETH GROUP REQUEST, method having (REQUEST = 'fc. OCEAN. JOB. SERVER. CBIZOZBKHEADER 'or request = 'fc. Ocean. Job. Server. CBizOzDocHeader ') And count (*)> 3

Notes for order by request:

The HAVING condition cannot use the alias COUNT> 3. You must use COUNT (*)> 3. Otherwise, the column name 'Count' is invalid.

Each element in the having clause does not necessarily appear in the select list.

If you write this statement as follows:

The Code is as follows:
Select count (*) as count, REQUEST, METHOD FROM REQUESTMETH GROUP REQUEST ORDER BY REQUEST

The following message is reported:

The 'requestmeth. method' column in the selection list is invalid because it is not included in the aggregate function or group by clause.

Note:
1. When using the group by clause, the non-summary column in the SELECT list must be an item in the group by list.
2. When grouping, all NULL values are grouped into one group.
3. Complex expression tests, display titles, and position labels in the SELECT list are not allowed in the group by list.

For example:

The Code is as follows:
Select request, METHOD, COUNT (*) AS COUNT FROM REQUESTMETH GROUP REQUEST, 2 ORDER BY REQUEST

Error message: each group by expression must contain at least one column reference.

Notes for using order by in group:

The Code is as follows:
Select count (*) as count from requestmeth group by request, method order by request, METHOD --

This is acceptable. The field after order by is included in the group by clause.

The Code is as follows:
Select count (*) as counts from requestmeth group by request order by count (*) DESC

-- This is acceptable. The fields after order by are included in the aggregate function, and the result set is the same as the following statement.

The Code is as follows:
Select count (*) AS COUNTS FROM REQUESTMETH GROUP BY REQUEST ORDER BY COUNTS DESC

-- This is acceptable. Unlike HAVING, HAVING does not allow aliases of clustering functions as filter conditions.

The Code is as follows:
Select count (*) as counts from requestmeth group by request order by method --

This is incorrect: the "REQUESTMETH. method" column in the order by clause is invalid because it is not included in the aggregate function or group by clause.

Note for using order by in select distinct:

The Code is as follows:
SELECT DISTINCT BOOKID FROM BOOK ORDER BY BOOKNAME

The preceding statement will report:

-- If select distinct is specified, the items in the order by clause must appear in the selection list.

Because the preceding statement is similar

The Code is as follows:
SELECT BOOKID FROM BOOK GROUP BY BOOKID ORDER BY BOOKNAME

In fact, the error message is:

The column "BOOK. BookName" in the -- order by clause is invalid because it is not included in the aggregate function or group by clause.

It should be changed:

The Code is as follows:
Select distinct bookid, bookname from book order by booknameselect distinct bookid, bookname from bookselect bookid, bookname from book group by bookid, BOOKNAME

The query results of the preceding two statements are consistent. The DISTINCT statement can be converted to the group by statement.