Oracle groups rows-groupby and having

Create a Data Group

All group functions treat the table as a large information group. However, you sometimes need to divide the table into several smaller groups. You can use the group by clause to complete this task.

Create a Data GROUP: group by clause syntaxYou can use the group by clause to divide rows in a table into smaller groups. SELECT column, group_function (column) FROM table [WHERE condition] [Group by group_by_expression][Order by column];

You can use the group by clause to divide rows in a table into groups. Then, you can use the group function to return the summary information of each group. In this syntax: group_by_expression specifies certain columns. Are the values of these columns used to determine the baseline for grouping rows? Unless a single column is specified in the group by clause, a single result cannot be selected even if the SELECT clause contains a GROUP function. If the list of columns is not included in the group by clause, an error message is returned. ? By using the WHERE clause, You can exclude certain rows before dividing rows into multiple groups. ? Columns must be included in the group by clause.? Column aliases cannot be used in the group by clause.

Use the group by clauseAll columns in the SELECT list that are not present in GROUP functions must be included in the group by clause. Hr @ TEST0924> SELECT department_id, AVG (salary) FROM employees group by department_id;
DEPARTMENT_ID AVG (SALARY) ------------------------ 100 8601.33333 30 4150 7000 20 9500 70 10000 90 19333.3333 110 10154 50 3475.55556 40 6500 80 8955.88235 10 4400 60 5760
12 rows selected.

When using the group by clause, make sure that all columns in the SELECT list that are not present in the GROUP function are included in the group by clause. The example shows the Department ID and average salary of each department. The following describes how to evaluate a SELECT statement containing a group by clause :? SELECT: the clause specifies the column to be retrieved, as shown in the following figure:-EMPLOYEES: average of all salaries in the GROUP specified BY the-group by: Clause in the table? FROM: clause specifies the table that the database must access: EMPLOYEES table. ? WHERE: the clause specifies the row to be retrieved. Because there is no WHERE clause, all rows are retrieved by default. ? Group by: the clause specifies how to GROUP rows. Because the row is grouped by Department number, the AVG Function Applied to the salary column calculates the average salary of each department. Note: to sort the query results in ascending or descending ORDER, include the order by clause in the query.

Use the group by clauseThe group by column does not have to appear in the SELECT list.Hr @ TEST0924> select avg (salary) FROM employees group by department_id;
AVG (SALARY) ----------- 8601.33333 4150 7000 9500 10000 19333.3333 10154 3475.55556 6500 8955.88235 4400 5760
12 rows selected.

The group by column does not have to appear in the SELECT clause. For example, the SELECT statement in the example shows the average salary of each department, but the corresponding department number is not displayed. However, if there is no department number, the result seems meaningless. You can also use the group function in the order by clause: Hr @ TEST0924> SELECT department_id, AVG (salary) FROM employees group by department_id order by avg (salary );
DEPARTMENT_ID AVG (SALARY) ------------------------ 50 3475.55556 30 4150 10 4400 60 5760 40 6500 7000 100 8601.33333 80 8955.88235 20 9500 70 10000 110 10154 90 19333.3333
12 rows selected.

Group by multiple columnsSometimes, you need to view the results of each group in the group.Hr @ TEST0924> SELECT department_id, job_id, sum (salary) FROM employees group by department_id, job_id order by job_id; DEPARTMENT_ID JOB_ID SUM (SALARY) ---------------------------------- 110 AC_ACCOUNT 8300 110 AC_MGR 12008 10 AD_ASST 4400 ... 20 rows selected.

This example shows a report showing the sum of salaries to be paid to each position in each department. The EMPLOYEES table is first grouped by Department number, and then grouped by position in each group. For example, divide four warehouse employees in department 50 into one group and generate a result (total salary) for all warehouse employees in the group ).

Use the group by clause for multiple columns Hr @ TEST0924> SELECT department_id, job_id, SUM (salary) FROM employees WHERE department_id> 40 group by department_id, job_id order by department_id;
DEPARTMENT_ID JOB_ID SUM (SALARY) ---------------------------------- 50 SH_CLERK 64300 50 ST_CLERK 55700 50 ST_MAN 36400 60 IT_PROG 28800 70 PR_REP 10000 80 SA_MAN 61000 80 SA_REP 243500 90 AD_PRES 24000 90 AD_VP 34000 100 FI_ACCOUNT 39600 100 FI_MGR 12008 110 AC_ACCOUNT 8300 110 AC_MGR 12008
13 rows selected.

BY listing multiple group by columns, you can return the summary results of the GROUP and sub-GROUP. The group by clause groups rows, but does not guarantee the order of result sets. To sort groups, use the order by clause. In the example, the SELECT statement containing the group by clause is evaluated as follows :? The SELECT clause specifies the columns to be retrieved: What is the sum of all salaries in the GROUP specified BY the "department ID"-"job ID"-"group by" clause in the EMPLOYEES table? The FROM clause specifies the table that the database must access: EMPLOYEES table. ? The WHERE Clause limits the result set to rows with a department ID greater than 40. ? The group by clause specifies how to GROUP result rows:-first, GROUP rows BY department ID-second, and GROUP rows BY job ID in the department id group? The order by clause sorts the results BY department ID. Note: The SUM function applies to the salary columns of all job IDs in the result set of each department ID group. In addition, note that the SA_REP row is not returned. The Department ID of this row is NULL, so the WHERE condition is not met.

Invalid Group function QueryAny column or expression in the SELECT list that is not in the aggregate function must appear in the group by clause: Hr @ TEST0924> SELECT department_id, COUNT (last_name) FROM employees; SELECT department_id, COUNT (last_name) FROM employees * ERROR at line 1: ORA-00937: not a single-group functionYou must add a group by clause to count the last names of each department_id.
Hr @ TEST0924> SELECT department_id, job_id, COUNT (last_name) FROM employees group by department_id; SELECT department_id, job_id, COUNT (last_name) FROM employees group by department_id * ERROR at line 1: ORA-00979: not a group by expression
You can either add job_id to group by or delete the job_id column from the SELECT list.

As long as a single item (DEPARTMENT_ID) and GROUP function (COUNT) are mixed in the same SELECT statement, a group by clause specifying these individual items (DEPARTMENT_ID in this example) must be included. If the group by clause is missing, the error message "not a single-group function (not a GROUP function)" appears )", an asterisk (*) pointing to the error column is displayed (*). You can add a group by clause to correct the errors in the first example:Hr @ TEST0924> SELECT department_id, count (last_name) FROM employees group by department_id;
DEPARTMENT_ID COUNT (LAST_NAME) ----------------------------- 100 6 30 6 1 20 2 70 1 90 3 110 2 50 45 40 1 80 34 10 1 60 5
12 rows selected.
Any column or expression in the SELECT list that is not in the aggregate function must appear in the group by clause.
In the second example, job_id is neither in the group by clause nor in the GROUP function. Therefore, the "not a GROUP BYexpression (not a group by expression)" error occurs. You can add job_id in the group by clause to correct the errors in the second example.Hr @ TEST0924> SELECT department_id, job_id, COUNT (last_name) FROM employees group by department_id, job_id;
DEPARTMENT_ID JOB_ID COUNT (LAST_NAME) --------------------------------------- 110 AC_ACCOUNT 1 90 AD_VP 2 50 ST_CLERK 20 ... 20 rows selected.

Invalid Group function Query ? You cannot use the WHERE clause to limit a group. Hr @ TEST0924> SELECT department_id, AVG (salary) FROM employees where avg (salary)> 8000 group by department_id; SELECT department_id, AVG (salary) FROM employees where avg (salary)> 8000 group by department_id * ERROR at line 1: The ORA-00934: group function is not allowed here

? You can use the HAVING clause to limit a group. ? You cannot use group functions in the WHERE clause.
You cannot use the WHERE clause to limit a group. The SELECT statement in the example produces an error because it uses the WHERE clause to limit the average salaries of those departments whose average salary is greater than $8,000. However, you can use the HAVING clause to limit the group to correct the error in this example:Hr @ TEST0924> SELECT department_id, AVG (salary) FROM employees group by department_id having avg (salary)> 8000;
DEPARTMENT_ID AVG (SALARY) ------------------------ 100 8601.33333 20 9500 70 10000 90 19333.3333 110 10154 80 8955.88235
6 rows selected.
Limit group resultsThe same way as using the WHERE clause to limit the selected row, you can use the HAVING clause to limit the group. To find the highest salary for each department with a maximum salary of more than $10,000, perform the following operations: 1. Group by Department number to find the highest salary for each department. 2. Set the group to a department with a maximum salary of more than $10,000.
Use HAVING clause to limit group resultsWhen the HAVING clause is used, Oracle Server limits the Group as follows: 1. Groups rows. 2. Apply group functions. 3. display groups that comply with the HAVING clause. SELECT column, group_function FROM table [WHERE condition] [group by group_by_expression][HAVING group_condition][Order by column];
You can use the HAVING clause to specify the group to be displayed. This clause further limits the group based on the summary information. In the preceding syntax, group_condition is used to limit the returned row group of a group that meets the specified conditions. When the HAVING clause is used, Oracle Server performs the following steps: 1. Grouping rows. 2. Apply group functions to the group. 3. display groups that meet the standards in the HAVING clause. HAVING clauses can be placed before the group by clause, but we recommend that you put the group by clause before it because it is more logical. You should first form a group and calculate the group function, and then apply the HAVING clause to the group in the SELECT list. Note:The WHERE Clause limits rows, while the HAVING Clause limits groups.
Use HAVING clause Hr @ TEST0924> SELECT department_id, MAX (salary) FROM employees group by department_id having max (salary)> 10000;
DEPARTMENT_ID MAX (SALARY) ------------------------ 100 12008 30 11000 20 13000 90 24000 110 12008 80 14000
6 rows selected.
The example shows the Department ID and maximum salary of a department with a maximum salary of more than $10,000. You can use the group by clause in the SELECT list without using GROUP functions. If the row is limited according to the result of the GROUP function, the group by clause and HAVING clause must be used. The following example shows the Department ID and average salary of a department with a maximum salary of more than $10,000:Hr @ TEST0924> SELECT department_id, AVG (salary) FROM employees group by department_id HAVING max (salary)> 10000;
DEPARTMENT_ID AVG (SALARY) ------------------------ 100 8601.33333 30 4150 20 9500 90 19333.3333 110 10154 80 8955.88235
6 rows selected.
Use HAVING clause Hr @ TEST0924> SELECT job_id, SUM (salary) payroll from employees WHERE job_id not like '% REP %' group by job_id having sum (salary)> 13000 order by sum (salary );
JOB_ID PAYROLL -------------------- PU_CLERK 13900 AD_PRES 24000 IT_PROG 28800 AD_VP 34000 ST_MAN 36400 FI_ACCOUNT 39600 ST_CLERK 55700 SA_MAN 61000 SH_CLERK 64300
9 rows selected.
The example shows the job ID and total monthly salary of each position whose total salary exceeds $13,000. In this example, the sales representative is excluded and the list is sorted by the total monthly salary.Nested group functionsThe following statements show the highest average salary:Hr @ TEST0924> select max (AVG (salary) FROM employees group by department_id;
MAX (AVG (SALARY )) ---------------- 19333.3333
A group function can be nested with two layers. The example calculates the average salary corresponding to each department_id and then displays the highest average salary. Note that the group by clause must be used for nested GROUP functions.