Detailed explanation of MySQL data grouping

Create Group

Grouping is established in the GROUP BY clause of the SELECT statement.

example:

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

GROUP BY

• The GROUP BY clause can contain any number of columns, which allows nested groupings and provides more detailed control over data grouping.
• If you nest groups in the GROUP BY clause, the data will be aggregated at the last specified grouping. In other words, when creating a grouping, all specified columns are calculated together. (So ​​data cannot be retrieved from individual columns).
• Each column listed in the GROUP BY clause must be a search column or a valid expression (but not an aggregate function). If you use an expression in the SELECT clause, you must specify the same expression in the GROUP BY clause. You cannot use an alias.
• Except for aggregate calculation statements, every column in the SELECT statement must be given in the GROUP BY clause.
• If there is a NULL value in the grouping column, NULL will be returned as a group. If there are multiple rows with NULL values ​​in the column, they will be grouped together.
• The GROUP BY clause must appear after the WHERE clause and before the ORDER BY clause.
  

Filter Groups

If one wants to list all orthopedics departments that have at least two orders, the data must be filtered based on the complete grouping rather than individual rows.

You can use HAVING

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

The difference between HAVING and WHERE is that WHERE filters before data grouping, while HAVING filters after grouping. Of course, both can appear in the same statement.

List suppliers with 2 or more products with a price of 10 or more

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

List suppliers with two top products

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

Grouping and Sorting

Retrieve the order number and total order price of orders with a total order price greater than or equal to 50

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

If you sort the output by total order price

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

Order of SELECT clauses

The above is a detailed explanation of MySQL data grouping. For more information about MySQL data grouping, please pay attention to other related articles on 123WORDPRESS.COM!