You can use the GROUPBY clause to divide rows into smaller groups in a SELECT statement, and then use the cluster function to return summary information for each group, and you can use the HAVING clause to restrict the returned result set. The GROUPBY clause can group query results and return the summary information for the rows. Oracle groups the query results by the value of the expression specified in the GroupBy clause.
In a query statement with a GROUPBY clause, the column specified in the select list is either the column specified in the GROUPBY clause or the cluster function
Copy Code code as follows:
Selectmax (SAL), Jobempgroupbyjob;
(Note that Max (SAL), job job does not necessarily appear, but meaningful)
The SELECT and groupby,having clauses of a query statement are the only places where a clustered function appears, and a clustered function cannot be used in the WHERE clause.
Copy Code code as follows:
Selectdeptno,sum (SAL) fromempwheresal>1200groupbydeptnohavingsum (SAL) >8500orderbydeptno;
When a HAVING clause is used in a gropuby clause, only the group that satisfies the having condition is returned in the query result. You can have a WHERE clause and a HAVING clause in an SQL statement. Having is similar to a WHERE clause and is used to set qualifications
The role of the WHERE clause is to remove rows that do not conform to the where condition before grouping the query results, that is, to filter the data before grouping, in conditions that cannot contain a clustered function, and where conditions are used 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 the clustering function, using the having condition to display a specific group, or multiple grouping criteria for grouping.
Inquire about the number of employees per position in each department
Copy Code code as follows:
Selectdeptno,job,count (*) fromempgroupbydeptno,job;
If you are still confused about when you should use a where and when to use it, follow the instructions below:
The where statement is preceded by a groupby statement, and SQL computes the where statement before grouping.
The having statement is after the GROUPBY statement, and SQL calculates the having statement after grouping.
