Oracle DB groups rows-group by 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 syntax

You 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 whose values determine the baseline for grouping rows unless a single column is specified in the group by clause, otherwise, 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 clause

All 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.3333330 4150700020 950070 1000090 19333.3333110 1015450 3475.5555640 650080 8955.8823510 440060 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 below:-EMPLOYEES: the Department number column in the table-group by: average of all salaries in the GROUP specified BY the clause • FROM: the 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 clause

The 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.333334150700095001000019333.3333101543475.5555665008955.8823544005760 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.5555630 415010 440060 576040 65007000100 8601.3333380 8955.8823520 950070 10000110 1015490 19333.3333 12 rows selected. 

• Group by multiple columns

Sometimes, 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 8300110 AC_MGR 1200810 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 6430050 ST_CLERK 5570050 ST_MAN 3640060 IT_PROG 2880070 PR_REP 1000080 SA_MAN 6100080 SA_REP 24350090 AD_PRES 2400090 AD_VP 34000100 FI_ACCOUNT 39600100 FI_MGR 12008110 AC_ACCOUNT 8300110 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 evaluates the value as follows: • SELECT clause specifies the column to be retrieved: -department ID in the EMPLOYEES table-sum of all salaries in the GROUP specified BY the job ID-group by clause in the EMPLOYEES table • 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, group rows BY job ID in the department ID group • 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. For more details, please continue to read the highlights on the next page:

Oracle grouping function rollup, cube

Oracle grouping function usage example

ROLLUP charm of Oracle grouping Functions

CUBE charm of Oracle grouping Functions

Oracle grouping query details

 

1 2 Next Page