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
Table of contents Preface Installation and Config...
As the domestic network environment continues to ...
Maybe you are using include files here, which is u...
This article shares the installation tutorial of ...
When using the docker-maven-plugin plug-in, Maven...
1. MySQL download address; http://ftp.ntu.edu.tw/...
Version 1.4.2 Official Documentation dockerhub st...
Why beautify the file control? Just imagine that a...
This article example shares the specific code of ...
This article is from Tom Ewer's Managewp blog,...
Problem description: When inserting Chinese chara...
This article shares the installation and configur...
premise In complex scenarios, a lot of data needs...
This article example shares the specific code of ...
First and foremost, I am a web designer. To be mor...