MySql014–GROUP BY clause & sorting ORDER BY & SELECT clause order

Prerequisite: Use the products table created in “MySql006 – Retrieval of data: basic select statements (using products table, querying single column, multiple columns, all columns, DISTINCT to remove duplicate rows, LIMIT to limit the number of rows in returned results, and understanding full qualification)”

1. Basic usage of GROUP BY clause

SELECT
    vend_id, COUNT(*) AS num_prods
FROM
    study.products
GROUP BY vend_id;

The above SELECT statement specifies two columns, vend_id contains the ID of the product supplier, and num_prods is a calculated field (created with the COUNT(*) function). GROUP BY clause refers to
Shows MySQL to sort and group data by vend_id. This causes num_prods to be calculated once for each vend_id instead of the entire table. As you can see from the output, supplier 1001 has 3 products, supplier 1002 has 2 products, supplier 1003 has 7 products, and supplier 1005 has 3 products.

Note:The GROUP BY clause must appear after the WHERE clause and before the ORDER BY clause.

2. Filter group

2.1. Preparation: Create the table orders (order table) in the study library and insert data

#####################
# Create orders table
#####################
use `study`;

CREATE TABLE orders
(
  order_num int NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL,
  cust_id int NOT NULL,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

#######################
# Populate orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);

2.2. Example 1: Want to list all customers with at least two orders.

SELECT
    cust_id, COUNT(*) AS orders
FROM
    study.orders
GROUP BY cust_id -- Use GROUP BY to group the same customer information into a group based on cust_id
HAVING COUNT(*) >= 2; -- Use HAVING and COUNT() to select customer information with an order number greater than or equal to 2

2.3, Example 2, which lists suppliers with 2 or more products with prices of 10 and above

SELECT
    vend_id, COUNT(*) AS num_prods
FROM
    study.products
WHERE
    prod_price >= 10 -- the price must be greater than or equal to 10
GROUP BY vend_id -- Group according to vend_id
HAVING COUNT(*) >= 2 -- Select only the data in the group that is greater than or equal to 2

# That is: group according to vend_id, only select data in the group with more than or equal to 2 pieces of data, and the price is greater than or equal to 10

3. The difference between grouping GROUP BY and sorting ORDER BY

3.1. Preparation: Create the table orderitems (order details table) in the study library and insert data

#########################
# Create orderitems table
#########################
CREATE TABLE orderitems
(
  order_num int NOT NULL,
  order_item int NOT NULL,
  prod_id char(10) NOT NULL,
  quantity int NOT NULL,
  item_price decimal(8,2) NOT NULL,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

#############################
# Populate orderitems table
#############################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

Go directly to the example

3.2, Example

It retrieves the order number and total order price of orders with a total order price greater than or equal to 50, and sorts the output by the total order price.

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

In this example, the GROUP BY clause is used to group the data by order number (order_num column) so that the SUM(*) function can return the total order price. The HAVING clause filters the data so that only orders with a total order price greater than or equal to 50 are returned. Finally, the output is sorted using the ORDER BY clause.

4. SELECT clause order

Let’s review the order of clauses in the SELECT statement

================================================ ==================
Clause Description Whether it must be used
================================================== ===============
SELECT Column or expression to return Yes
FROM table from which to retrieve data. Used only when selecting data from a table.
WHERE Row-level filtering No
GROUP BY grouping description is only used when calculating aggregation by group
HAVING Group level filtering No
ORDER BY output sort order No
LIMIT Number of rows to retrieve No
================================================== ===============

Share with you

?Finally, interested friends can click on the link below. Here is the MySQL learning blog content I compiled, thank you ~

“MySQL Database Learning”