Use of GROUP BY

1 group BY is a grouped query, and the general group by IS used in conjunction with aggregate functions

Group by has a principle that columns in the column following the select Do not use the aggregate function (AVG () sum (), COUNT () Max (), MIN ()---), and must appear behind group by.

Select Ename,count (empno), deptno from EMP Group by Deptno,ename; Correct wording.

Select Ename,count (empno), deptno from EMP Group by DEPTNO; Wrong wording. Columns that do not use the grouping function must appear behind GROUP by.

2 having

The purpose of the WHERE clause is to remove rows that do not conform to the where condition before grouping the results of the query, that is, to filter the data before grouping, the condition cannot contain a clustering function, and a where condition to display a particular row.

The HAVING clause is used to filter the groups that meet the criteria, that is, to filter the data after grouping, often including clustering functions, to display specific groups using the having condition, or to group by using multiple grouping criteria.

The HAVING clause restricts the columns and aggregate expressions that have been defined in the SELECT statement. Typically, you need to reference an aggregate value by repeating an aggregate function expression in the HAVING clause, as you did in the SELECT statement.

For example:

Select Sal,ename,deptno from emp where ename like ' m% ' GROUP by Ename,sal,deptno have Sal >100;

Use of GROUP BY