mysql data grouping: filter grouping
mysql filter grouping
In addition to grouping data using group by, MySQL also allows filtering groups, stipulating which groups are included and which groups are excluded. For example, you might want to list all customers who have at least two orders. To derive this data, filtering must be done based on complete groups rather than individuals.
We have already seen the where clause in action. However, where does not accomplish the task in this example because the where filter specifies rows rather than groups. In fact, where has no concept of grouping.
So, what to use instead of where? MySQL provides another clause for this purpose, which is the having clause. having is very similar to where. In fact, all types of where clauses you have learned so far can be replaced by having. The only difference is where filters rows, while having filters groups.
Note: having supports all where operators.
So, how to filter groups? Please look at the example below:
Input:
select cust_id,count(*) as orders from orders group by cust_id having count(*) >= 2;
Output:
Analysis: The first 3 lines of this select statement are similar to the above statement. The last line adds a having clause, which filters those groups with count(*>)>=2 (more than two orders).
As you can see, the where clause does not work here because the filtering is based on the grouped aggregate value and not the specific row value.
The difference between having and where: Here is another way to understand it, where filters before data grouping, and having filters after data grouping. This is an important distinction, rows excluded by where are not included in the grouping. This may change the calculated values, thereby affecting the groupings in the having clause that are filtered out based on these values.
So, is there any need to use both where and having clauses in one statement? In fact, there is. Suppose you want to further filter the above statement so that it returns more than two customers in the past 12 months. To achieve this, you can add a where clause to filter out orders placed within 12 months. Then add a having clause to filter out groups with more than two orders.
For better understanding, please see the example below, which lists suppliers with products above 2 (inclusive) and price 10 (inclusive) or above:
Input:
select vend_id,count(*) as num_prods from products where prod_price >= 10 group by vend_id having count(*) >=2;
Output:
Analysis: In this statement, the first line is a basic SELECT using an aggregate function, which is very similar to the previous example. The WHERE clause filters all rows where prod_price is at least 10. The data is then grouped by vend_id and the HAVING clause filters groupings with a count of 2 or above.
If there is no WHERE clause, two more rows will be retrieved (supplier 1002, all products sold byprices are below 10; Supplier 1001, selling 3 products, but only one product has a higher price equal to 10):
Input: select vend_id,count(*) as num_prods from products group by vend_id having count(*) >=2;Output:
The above is the detailed content of mysql data grouping: filter grouping. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to use Vue and Element-UI to group and summarize data
Jul 21, 2023 pm 02:37 PM
How to use Vue and Element-UI to group and summarize data. In front-end development, we often encounter situations where we need to group and summarize data. Vue is a very popular JavaScript framework, and Element-UI is a component library based on Vue. It provides a rich set of UI components that can help us quickly build pages. This article will introduce how to use Vue and Element-UI to group and summarize data, and illustrate it with code examples. Preparatory work
How to use vue and Element-plus to group and sort data
Jul 18, 2023 am 10:39 AM
How to use Vue and ElementPlus to group and sort data Vue is a popular JavaScript framework that can help us build front-end applications. ElementPlus is a desktop component library based on Vue. It provides a rich set of UI components, allowing us to easily build beautiful and user-friendly interfaces. In this article, we will explore how to use Vue and ElementPlus to group and sort data. First, we need to prepare a
How to optimize the efficiency of data sorting and data grouping in PHP and MySQL through indexes?
Oct 15, 2023 pm 04:00 PM
How to optimize the efficiency of data sorting and data grouping in PHP and MySQL through indexes? In the process of developing web applications, it is often necessary to sort and group data. For data sorting and data grouping operations between PHP and MySQL, we can optimize its efficiency through indexes. An index is a data structure used to speed up the retrieval of data. It speeds up sorting, grouping, and lookup operations on data. Below we will introduce how to optimize data sorting and data grouping of PHP and MySQL through indexes.
ECharts histogram (multidimensional): how to display data grouping and comparison
Dec 18, 2023 pm 12:52 PM
ECharts histogram (multidimensional): How to display data grouping and comparison requires specific code examples. ECharts is an open source visualization library based on JavaScript, used to display various types of data charts. The histogram is a common data visualization method that can be used to display the grouping and comparison of data in different groups or categories. This article will introduce in detail how to use the multi-dimensional histogram function of ECharts to display data grouping and comparison, and provide specific code examples for readers' reference. 1. EChar
PHP and PDO: How to group data and aggregate calculations
Jul 28, 2023 pm 04:36 PM
PHP and PDO: How to perform data grouping and aggregation calculations Introduction: When developing web applications, you often encounter situations where you need to group and aggregate data in the database. It is a common practice in PHP to use the PDO (PHPDataObjects) extension to connect and operate databases. This article will introduce how to use PDO for data grouping and aggregation calculations, and provide corresponding code examples. Connect to the database First, we need to use PDO to connect to the database. Here is an example
How to use PHP and Vue to implement data grouping function
Sep 25, 2023 am 09:18 AM
Introduction to how to use PHP and Vue to implement the data grouping function: When developing web applications, you often encounter situations where data needs to be grouped. The data grouping function can be easily implemented using PHP and Vue. This article will introduce step by step how to use PHP and Vue to implement data grouping, and provide specific code examples. 1. Preparation: Install PHP and Vue environments. Create a folder named "grouping" project and create the following file and folder structure: groupingindex
How to group and filter table data through Vue and Excel
Jul 21, 2023 pm 03:34 PM
How to group and filter tabular data through Vue and Excel Introduction: In many practical applications, we often need to group and filter tabular data in order to better analyze and understand the data. This article will introduce how to implement the grouping and filtering functions of tabular data through Vue and Excel, and provide corresponding code examples to help readers get started quickly. Basic concepts and environment configuration of Vue Vue is a progressive framework for building user interfaces, which is simple and efficient. Before starting the operation, we first
MySQL database and Go language: How to group data?
Jun 17, 2023 pm 01:41 PM
As the amount of data increases and business requirements become more complex, data processing has become a necessary and important task. In modern web development, MySQL database and Go language have become one of the preferred technologies for developers. The MySQL database is used to store and manage data, while the Go language is used to process the data. This article will introduce how to use MySQL database and Go language for data grouping processing. Database design First, we need to design a database structure suitable for data grouping processing. Suppose we are building an e-commerce website
