Oracle Grouped data

Grouping allows data to be divided into logical groups , So that aggregation calculations can be performed on each group .

1、 Create grouping

The group is in SELECT Use in statement GROUP BY Clause creates .

Example ：

SELECT vend_id, count(*) as num_prods
from products
group by vend_id;

Due to the use of GROUP BY, It will not be necessary to specify each grouping to be evaluated and calculated , This is done automatically .GROUP BY Clause indicates Oracle Group data , And group each （ Not for the entire result set ） Perform aggregation .

In the use of GROUP BY Before , Here are some important rules you need to know about using it .

• GROUP BY Clauses can contain many columns as needed . It allows nested grouping , More fine-grained control over the grouping of data .
• If in group by Clause has nested groupings , The data will be summarized on the last designated group . let me put it another way , When creating a group , All specified columns will be evaluated together （ therefore , Data will not be retrieved for each individual column ）.
• group by Each column listed in must be a retrieved column or a valid expression （ Instead of a 1 Aggregate functions ）. If in select Use an expression in , Then it must be in group by The same expression is specified in . Cannot use alias .
• In addition to aggregate calculation statements ,SELECT Every column in the statement should appear in GROUP BY clause .
• If the grouping column contains a with NULL Columns of values , Will return NULL, As a group . If there are multiple lines with NULL value , They will be grouped together .
• GROUP BY Clause must appear in WHERE After the clause and ORDER BY Before clause .

2、 Filter grouping

where Clauses are also commonly used for row filtering . however ,where Not applicable here , because where You can filter specific lines , Instead of grouping . in fact ,where Cannot be applied to grouping .

Oracle Another clause is provided for this purpose ：HAVING.where Clause and having The only difference between clauses is ：where Filter line , and having Then filter the grouping .

** Tips ：**having Support all where Operator

where And having The rules of , The grammar is exactly the same , Only the key is different .

Example ：

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

Be careful ：having And where The difference between

Look at it from another angle having And where The difference between ：where Filtering occurs before data , and having Filtering occurs after data grouping . This is an important difference , By where The rows deleted by clause will not be included in the grouping . This may change the calculated value , be based on having The values used in Clause , In turn, it may affect which groups will be filtered .

where Clause and having Clause with examples ：

select vend_id, count(*), as num_prods
from products
where prod_price>=10
group by vend_id
having count(*) > 2;

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >= 2;

3、 Grouping and sorting

group by and order by There's a big difference , Even though they are usually used to accomplish the same thing .

The following table describes order by And group by The difference between

Often , You will find the use of GROUP BY The grouped data is indeed output in grouping order . But not always , And actually SQL Specifications do not require this . And you may actually want it to sort in a different way than grouping . You group data in one way （ To get group specific aggregate values ）, It doesn't mean that you also want the output to be sorted in the same way . Always provide an explicit ORDER BY Clause , Even if it's GROUP BY The clauses are exactly the same .

Tips ： Don't forget it ORDER BY

Usually , Whenever used GROUP BY Clause , You should also specify a ORDER BY Clause , This is the only way to ensure that the data is sorted correctly . Never rely on GROUP BY Sort the data .

To demonstrate the simultaneous use of GROUP BY and ORDER BY The situation of , Let's look at an example . Below SELECT The statement is similar to the one used previously SELECT sentence . It is used to retrieve the total price in 50 above （ contain 50） Order number and total price of all orders ：

SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50;

Sort the output by total order price , Just add one ORDER BY Clause , as follows ：

SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal;

4、select Clause ordering

select Clauses and their order