MySQL learning footprint record 11 -- GROUP data -- group by, HAVING

MySQL learning footprint record 11 -- GROUP data -- group by, HAVING 1. Create a group by to list all vend_id for comparison

Mysql> SELECT vend_id FROM products; + --------- + | vend_id | + --------- + | 1001 | 1001 | 1001 | 1002 | 1002 | 1003 | 1003 | 1003 | 1003 | 1003 | 1003 | 1003 | 1005 | 1005 | + --------- + 14 rows in set (0.00 sec) use group by to GROUP mysql> SELECT vend_id, COUNT (*) AS num_prods-> FROM products # GROUP first, and then calculate COUNT (*)-> group by vend_id; + --------- + ----------- + | vend_id | num_prods | + --------- + ----------- + | 1001 | 3 | 1002 | 2 | 1003 | 7 | 1005 | 2 | + --------- + ----------- + 4 rows in set (0.00 sec)

 

TIPS: * If there are multiple NULL values in the column, they will be divided into a GROUP * the group by clause must appear after the WHERE clause, before the order by clause 2. the difference between HAVING * HAVING and WHERE: WHERE filters rows, while HAVING filters groups.

Eg: mysql> SELECT vend_id, COUNT (*) AS num_prods-> FROM products-> group by vend_id-> having count (*)> 2; # filter results that do not match COUNT (*)> group 2 + --------- + ----------- + | vend_id | num_prods | + --------- + ----------- + | 1001 | 3 | 1003 | 7 | + --------- + 2 rows in set (0.00 sec)

 

* WHERE is used to filter data groups and HAVING is used to filter data groups. Therefore, rows excluded from WHERE are not included in the group.

Eg: first list the raw data for comparison mysql> SELECT vend_id, prod_price FROM products-> order by prod_price; + --------- + ------------ + | vend_id | prod_price | + --------- + ------------ + | 1003 | 2.50 | 1003 | 2.50 | 1002 | 3.42 | 1003 | | 1002 | 8.99 | 1001 | 9.99 | 1003 | 10.00 | 1003 | 10.00 | 1003 | 13.00 | 1001 | 14.99 | 1005 | 35.00 | 1003 | 50.00 | | 1005 | 55.00 | + --------- + ------------ + 14 rows in set (0.00 sec) mysql> SELECT vend_id, COUNT (*) AS num_prods-> FROM products-> WHERE prod_price> 14 # After WHERE filtering, only the last three records in the table above are left, -> group by vend_id # HAVING: after the GROUP is filtered again, the vend_id does not match COUNT (*)> = 2 groups-> having count (*)> = 2; + --------- + ----------- + | vend_id | num_prods | + --------- + ----------- + | 1005 | 2 | + --------- + ----------- + 1 row in set (0.00 sec)

 

3. difference between grouping and sorting group by and order by * the condition specified by order by can be any column * the condition specified by group by can only be selected column or column expression TIPS: generally, when using the group by clause, the order by clause Examples should also be given to list the original data first:

Mysql> SELECT order_num, quantity, item_price FROM orderitems; + ----------- + ---------- + ------------ + | order_num | quantity | item_price | + ----------- + ---------- + ------------ + | 20005 | 10 | 5.99 | 3 | 20005 | 9.99 | 5 | 10.00 | 20005 | 1 | 10.00 | 20006 | 1 | 55.00 | 20007 | 100 | 10.00 | 20008 | 50 | 2.50 | 20009 | 1 | 10.00 | 20009 | 1 | 8.99 | 20009 | 1 | 4.49 | 20009 | 1 | 14.99 | + ----------- + ---------- + ------------ + 11 rows in set (0.00 sec) mysql> SELECT order_num, SUM (quantity * item_price) AS ordertotal-> FROM orderitems-> group by order_num-> having sum (quantity * item_price)> = 50; + ----------- + ------------ + # The sorting is not specified using ORDERBY, and the result may not be desired, for example, in ascending order of ordertotal | order_num | ordertotal | + ----------- + ------------ + | 20005 | 149.87 | 20006 | 55.00 | 20007 | 1000.00 | 20008 | + ----------- + ------------ + 4 rows in set (0.00 sec) mysql> SELECT order_num, SUM (quantity * item_price) AS ordertotal-> FROM orderitems-> group by order_num-> having sum (quantity * item_price)> = 50-> order by ordertotal; # Use ORDERBY to specify the sorting method + ----------- + ------------ + | order_num | ordertotal | + ----------- + ------------ + | 20006 | 55.00 | 20008 | 125.00 | 20005 | 149.87 | 20007 | 1000.00 | | + ----------- + ------------ + 4 rows in set (0.01 sec)