SQL Group by summary for Oracle databases

SQL Group by summary for Oracle databases

Preface
As mentioned above, grouping Functions Act on a group of records. What can this group of records be generated? Impossible
They are all in the form of a whole table. This requires the Group by clause.
Group
Group by clause:
● The Group by clause divides a table into multiple groups and returns a calculated value for each Group.
● Group by expression: indicates the column by which the Group is made.
Note:
● If the grouping function is used in the select clause, the grouping function cannot be used for the columns specified by group.
● Using the where clause can pre-exclude certain records
● The Group by clause must contain columns in the table.
● The Group by clause cannot use aliases.
● The Order by clause can be used to change its sorting status.
Basic usage
Its basic usage is directly presented as an instance.
1. Count the total salaries of employees in various departments [SQL]

Select deptno, sum (sal) from emp group by deptno;
-- Sort in ascending order
Select deptno, sum (sal) from emp group by deptno order by deptno asc; 2. Count the total wages of employees in various professions in each department. [SQL]

Select deptno, job, sum (sal) from emp
Group by (deptno, job) order by deptno; Tips: the second instance is actually a multi-column grouping. Groups departments first, and then positions.

 
3. Use of Having clauses
We know that the grouping function Cannot uninstall the WHERE clause, but sometimes we need to limit the grouping.
Only groups meeting a specific requirement can be selected, and can be selected through the having clause. Usage
It is also described as an instance.
Add a condition on the basis of 2: the total salary must be above 10000 [SQL]

Select deptno, sum (sal) from emp group by deptno
Having sum (sal) & gt; 10000;
Tips: Think about the difference between Having and where clauses.

Both of them filter the data. The difference is that where filters the original data while having filters the summary data.
The result is filtered!
Extended usage
In addition to the basic usage, group by also has some extended usage, but in most cases, the basic usage is basically
This will satisfy our operations.
1. Use the rollup Operator
Rollup is an extension of the group by clause. It can return subtotal records for each group and return subtotal records for all groups.
Total records. Let's take a look at its basic usage.
⊙ Pass a [SQL] column to rollup

Select deptno, sum (sal) from emp where DEPTNO> = 20 group by rollup (deptno );

However, if you want to make a total for all records, you need an aggregate function.
Otherwise there is no practical significance at all!
⊙ Pass multiple columns to rollup
Tips: It should be noted that when rollup acts on multiple columns, it takes effect for the first column! [SQL]

<Span style = "color: #000000;"> select deptno, job, sum (sal) from emp group by rollup (deptno, job );
Select deptno, job, sum (sal) from emp group by rollup (job, deptno); </span>
It can be seen that, except for the last sum, each deptno has a subtotal.

The principle of the result of column exchange is the same, so we will not demonstrate it here.
 
2. Use the cube Operator

Cube is also an extension of the Group by clause. It returns the subtotal record of each column combination, and adds
Total records. (Oracle 11g) seems different from before?
⊙ Pass a [SQL] column to cube

Select deptno, sum (sal) from emp group by cube (deptno );

The effect looks similar to rollup, but the total position has changed. Don't worry about multiple columns!
⊙ Transfer multiple columns to cube [SQL]

Select deptno, job, sum (sal) from emp group by cube (deptno, job );
It can be seen that cube returns a record (total salary of all departments) in each deptno, and

A subtotal is made for the total wages of each job, and a subtotal is made for the wages of each job (no department restrictions), and
Make a sum of all the wages.
Summary of differences between cube and rollup:
Through the above learning, we may feel that the combination of cube and rollup is different intuitively,
You can distinguish from this aspect, but it is best not to think about the problem from this aspect.
ROLLUP (a, B, c... n)
Group by (a, B, c)-> (a, B)-> (a)-> full table group by groups n + 1 times
CUBE (a, B, c... n)
Group by (a, B, c)-> (a, B)-> (a, c)-> (a)-> (B, c)-(B) -(c)-> the entire table group by groups to the power of 2n
As for other extension usage, we will not display it first, and we will encounter it later! Over!