1. Create Group Byselect ven_id,count (*) as Num_prods from the products group by VEN_ID; Note: Group by can contain any number of columns each column listed by the group must be retrieved A column or a valid expression (but not a clustered function). If you use an expression in a SELECT statement, you must specify the same expression in group by, and you cannot use an alias. Each column in a SELECT statement must be given in the GROUP BY clause in addition to the clustered calculation statement. GROUP by must appear after the where statement before the ORDER by statement. 2. Filter group having filter group, where filter row. SELECT cust_id, COUNT (*) as Ordersfrom orders GROUP by cust_idhaving COUNT (*) >=2;count (*) >=2--filter more than 2 order grouping examples: list with 2 (including) above, the price is 10 (including) the product of the supplier Select cust_id, COUNT (*) as Num_prodsfrom productswhere Prod_price>=10group by Ven_ Idhaving COUNT (*) >=2; 3, grouping and sorting examples: Retrieving order number and total order price for total order price greater than or equal to 50 select Order_num,sum (Quantiy*item_price) as Ordertotalfrom Orderitemsgroup by Order_numhaving SUM (Quantiy*item_price) >=50; To sort the output by a total order price, you need to add an order BY statement as follows: SELECT Order_num,sum (Quantiy*item_price) as Ordertotalfrom Orderitemsgroup by Order_ Numhaving SUM (Quantiy*item_price) >=50order by OrderTotal;
MYSQL Learning Notes Collation two: grouping data
