Oracle Operation Summary

Aggregate functions:
Count sum max min avg
Count the number of rows in the returned result set
Sum max min avg. However, the aggregate function emphasizes "result set ...... ", Emphasized
The current operation object is the result set.
Note that NULL does not participate in the aggregate function budget. To participate in the calculation, convert it to a non-empty value.

GROUP:
Grouping is required if multiple statistics are required in a query.

Where is used to filter data before grouping, and raw data is used to filter data. In this way, unnecessary data is filtered out to improve efficiency.

Having is post-grouping filtering, and filtering is the statistical result

First, you need to understand: group, group what is called a group-the premise is of course the same is called a group

For example, the class members are divided into two groups based on gender. Only men are in the same group.

The instructor's note is very clear: grouping function operations each group of records, each group returns a result
For example, select deptno, job, avg (sal) from emp group by deptno. The job regards deptno and job as the same (the other fields do not need to be considered much, so the two fields are the key) count as a group
Therefore, Oracle returns nine rows of data, that is, nine results.

Note that After grouping by certain fields, the concept of "group" is gone, and the individual features have disappeared.
By which group, you can obtain these fields from the result set !!! For example, you can obtain the city names of each group by grouping cities.
In addition to grouping fields, other fields can only be involved in aggregation (count sum max min avg) -- of course, the premise is that you can use specific aggregation
Function. For example, avg (name) is not allowed, because it is not a number !!!!!!!!!!

Emphasize again: for example (Here we suppose there is no comm group) the employee number is "9527" Comm (Commission), but the average comm can be obtained.
Because the concept of "group" exists now. No longer exists.

Core Idea: After grouping, the concept of individual disappears. It is a concept of "group.
For example, the Student name, age, gender, and height are displayed.
Group by height-the same height is in one group. Of course, you can select the height, and the rest can only use the aggregate function.
Group by gender-put the same gender in a group. Of course, you can select gender, and the rest can only use aggregate functions.

It is worth noting that if aggregation is involved, the order by field can only be a field in the group when grouping !!!!!!!!!!!!!!

Groups must be considered as long as aggregation functions are involved !!!!!!!!!!!
If an aggregate function is present and group by is not involved, this is the most special group. Any specific field in the SELECT statement cannot be used.
Only Aggregate functions can be used.

Here we have a bunch of lab data for these two days:

Select * from EMP
Select job, count (*) from EMP group by job having count (*)> 3
Select job "job type" from EMP where job = 'cler' -- correct
Select job "job type", count (job) "job count" from EMP group by job -- correct
Select count (*) "Number of students in this job" from EMP where job = 'cler' -- correct
Select job "job type", count (*) "job type count" from EMP where job = 'cler' -- incorrect
Select job "job type", sum (comm) from emp group by job -- correct
Select job "type", avg (comm) from emp group by job -- correct
Select job "job type", max (comm) from emp group by job -- correct
Select job "type", min (comm) from emp group by job -- correct
Select job "type", sal "salary", sum (comm) from emp group by job -- incorrect
Select job "job type", avg (sal) "average salary", sum (comm) from emp group by job -- correct
Select job "job type", max (sal) "highest salary", sum (comm) from emp group by job -- correct
Select job "job type", max (mgr) "Manager is", sum (comm) from emp group by job -- correct
Select job "job type", sum (mgr) "test", sum (comm) from emp group by job -- correct
Select job "type", max (ename) "Maximum ename", sum (comm) from emp group by job -- correct

Select job "job type", count (mgr) "test", sum (comm) from emp group by job -- correct
Select job "job type", count (ename) "test", sum (comm) from emp group by job -- correct
Select job "job type", count (hiredate) "test", sum (Comm) from EMP group by job -- correct
-- About the order after grouping
Select job "job type", max (SAL) "highest salary", sum (Comm) from EMP group by job order by job desc -- correct
Select job "job type", max (SAL) "highest salary", sum (Comm) from EMP group by job order by comm desc -- incorrect
Select job "job type", max (SAL) "highest salary", comm "Commission", sum (Comm) from EMP group by job, comm order by comm desc -- correct

Select * from EMP
-- 1 clerk null
-- 2 Sale 300
-- 3 Sale 500
-- 4man null
-- 5 Sale 1400
-- 6man null
-- 7man null
-- 8ana null
-- 9pre null
-- 10sal 0
-- 11 clerk null
-- 12 clerk null