Oracle _ Common grouping functions and oracle grouping Functions

Oracle _ Common grouping functions and oracle grouping Functions
Zookeeper

Oracle _ Common grouping Functions
① Grouping Function
1. max (column): calculates the maximum value, which is not required for the data type. Any data type can be used.
2. min (column): calculates the minimum value and has no requirements on the data type. Any data type can be used.
3. avg (column): returns the average value of column not null, which has requirements on the Data Type and can only be for the number type (number type)
4. sum (column): returns the sum of columns not null. It has requirements on data types and can only be set to number (number type)
5. count (column): return the number of records whose column is not null. No data type is required. Any data type can be used.

Note: Group functions ignore null values.
COUNT (DISTINCT expr) returns the total number of records that are not empty and repeat

② Grouping
Group data: group by clause syntax
You can use the group by clause to divide data in a table into several groups.
All columns not included in the GROUP function in the SELECT list should be included in the group by clause.
Columns included in the group by clause do not need to be included in the SELECT list
All columns included in the select list and not included in GROUP functions must be included in the group by clause.

You cannot use group functions in the WHERE clause.
You can use group functions in the HAVING clause.

③ Filter group: HAVING clause
Use HAVING to filter groups:
1. rows have been grouped.
2. Group functions are used.
3. Groups that meet the conditions in the HAVING clause will be displayed.

④ Group function nesting
Max (avg (salary ))

What grouping functions are used in oracle?

Commonly used:
COUNT () returns the number of rows to be searched.
Example: select count (*) from table;
MAX () returns the maximum value of the expression.
Example: select a, max (B) from table group by;
MIN () returns the minimum value of the expression.
Example: select a, min (B) from table group by;
SUM () returns the SUM of the expressions.
Example: select a, sum (B) from table group by;
AVG () returns the average value of the expression.
Example: select a, avg (B) from table group by;
In addition, there is an analytical function over, which is used to process complex SQL statements. This involves a lot of things. One or two statements are unclear. If you have any questions about this, you can ask for help or ask questions.

Oracle grouping Functions

The second is incorrect. The second is to group a and B at the same time. You can see the following example in detail:
Create table test
(A int,
B int,
C int );

Insert into test values (1, 2)
Insert into test values (1, 2 );
Insert into test values (1, 3 );
Insert into test values (1, 3, 2 );
Insert into test values (2, 2, 1 );
Insert into test values (2, 2 );
Insert into test values (3, 2, 3 );
Insert into test values (4, 2, 4 );

Select a, count (*) "Statistics" from test t
Group by t.;

Select a, B, count (*) "Statistics" from test t
Group by t. a, t. B;