SQL: group data in Chapter 10. SQL: Required

SQL: group data in Chapter 10. SQL: Required
10.1 Data grouping

Groups allow data to be divided into multiple logical groups so that each group can be clustered and computed.

10.2 create a group

SELECT vend_id,COUNT(*) AS num_prodsFROM ProductsGROUP BY vend_id;

Rules for using group:
(1) The group by clause can contain any number of columns. This allows grouping to be nested and provides more detailed control for data groups.
(2) If a GROUP is nested in the group by clause, the data will be summarized in the last defined GROUP. In other words, when a group is created, all specified columns are calculated together.
(3) Each column listed in the group by clause must be a search column or a valid expression. If an expression is used in SELECT, the same expression must be specified in the group by clause. Aliases cannot be used.
(4) Most SQL statements do not allow a GROUP BY column to have a variable data type.
(5) Except for the aggregate calculation statement, each column in the SELECT statement must be given in the group by clause.
(6) If the group column type has a NULL value, NULL is returned as a group. If the column type has multiple NULL values, they are divided into one group.
(7) The group by clause must appear after the WHERE clause and before the order by clause.

10.3 filter groups

WHERE filter specifies columns rather than groups. In fact, the WHERE clause does not have the concept of grouping.
In fact, all types of WHERE clauses that have been learned can be replaced by HAVING. The only difference is that the WHERE clause filters rows, while the HAVING clause filters rows.

SELECT cust_id,COUNT(*) AS ordersFROM OrdersGROUP BY cust_idHAVING COUNT(*) >= 2;

Differences between HAVING and WHERE: there is another way to understand it. WHERE is used to filter data before grouping, and HAVING is used to filter data After grouping. This is an important difference. The Rows excluded by the WHERE clause are not included in the group. This may change the calculated values and affect the groups filtered out based on these values in the HAVING clause.
List suppliers with more than two products at a price of 4

SELECT vend_id,COUNT(*) AS num_prodsFROM ProductsWHERE prod_price >= 4GROUP BY vend_idHAVING COUNT(*) >= 2;

HAVING and WHERE: HAVING are very similar to WHERE. If group by is not specified, most DBMS treat them as the same thing. However, for the sake of differentiation, HAVING should be used only when used in conjunction with the group by clause, while the WHERE clause is used for standard row-level filtering.

10.4 grouping and sorting

Difference between group by and ORDER

Do not forget order by: Generally, the order by clause should also be given when the group by clause is used. This is the only way to ensure correct data sorting. Do not rely only on group by to sort data.
Search the order number and the number of order items that contain more than 3 items

SELECT order_num,COUNT(*) AS itemsFROM OrderItemsGROUP BY order_numHAVING COUNT(*) >= 3;

Order by clause is required for sorting the output BY the number of purchased items.

SELECT order_num,COUNT(*) AS itemsFROM OrderItemsGROUP BY order_numHAVING COUNT(*) >= 3ORDER BY items,order_num;

Order of the 10.5SELECT clause