Create GroupGrouping 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
Filter GroupsIf 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! You may also be interested in:
|
<<: Analysis of the usage of Xmeter API interface testing tool
>>: Markup Language - Image Replacement
introduction With the widespread popularity of In...
The main configuration file of Nginx is nginx.con...
Table of contents 1. Installation of JDK1.8 under...
Table of contents Preface: Step 1: Find the free ...
Prerequisite: Percona 5.6 version, transaction is...
Table of contents 1. Introduction 2. Deployment E...
This article is based on Linux centos8 to install...
I encountered mysql ERROR 1045 and spent a long t...
Problem/failure/scenario/requirement The hard dis...
Only show the top border <table frame=above>...
String extraction without delimiters Question Req...
Are you still using rem flexible layout? Does it ...
Steps to configure whitelist access in mysql 1. L...
When setting display:flex, justify-content: space...
This article shares the installation and configur...