OracleDB groups rows-groupby and having

OracleDB groups rows-groupby and having. All group functions treat the table as a large information group. However, you sometimes need to divide the table into several smaller groups.

Oracle DB groups rows by group by and having. All group functions treat the table as a large information group. However, you sometimes need to divide the table into several smaller groups.

Oracle DB groups rows-group by and having

[Date:] Source: Linux community Author: article [Font:]

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. The values of these columns 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 a GROUP function is included in the SELECT clause. 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.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 the SELECT statement of the group by clause:

• SELECT: the clause specifies the column to be retrieved, as shown below:

-EMPLOYEES: department ID column in the table

-Group by: average of all salaries in the GROUP specified BY the clause

• 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 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.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 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 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:

• SELECT clause specifies the column to be retrieved:

-Department ID in the EMPLOYEES table

-Job ID in the EMPLOYEES table

-Sum of all salaries in the GROUP specified BY the group by clause

• 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:

-Group rows by department ID

-Second, 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.

For more details, please continue to read the highlights on the next page:

Related reading:

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

0

Oracle DB uses a connection to display data in multiple tables

Oracle DB TO_CHAR, TO_DATE, and TO_NUMBER Functions