“This is the 19th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021”

group by

The keyword that groups data in mysql needs to be queried with aggregation functions such as count, AVg, Max, and min. The SQL template statement is

SELECT groupName, count(groupName) FROM table GROUP BY groupName
Copy the code

Take count as an example. Group the table by groupName, return the groupName of the grouped field, and summarize the data of the row records corresponding to this field

Here’s an example of a beginner’s mistake:

Translation: We use the group by operation for non-aggregated fields

The general operation of group by is to group a field and count how many records in that field have the same value, as in the example at the end of this article

Or, we calculate the highest/lowest/average salary in each department, pseudo SQL is as follows:

select dept, max|min|avg(dept) from salary group by dept
Copy the code

having

Mysql provides the conditional query keyword WHERE to help filter out records that do not meet the criteria

However, where does nothing to aggregate grouped query results, so you need to use HAVING to implement conditional filtering

Here are the differences between where and having:

  • Where helps us filter out records that do not meet the criteria before we retrieve the results of the query
  • Having is to filter the existing results after we get the results of the querygroup byCollocation is used

Case sharing

In a certain factory, after we launched our system and ran it for a period of time, we found that there was an anomaly of repeated codes in the data. The person in charge of the factory needed to help find out which ones were repeated codes from the database

To analyze this scenario, we first group the code field and count the frequency of its occurrence, and then filter the data whose frequency exceeds 1 again. The code pulled by this operation is the data expected by the customer

The SQL is roughly as follows

SELECT
	CODE,
	count(CODE) count
FROM
	rfid_info
WHERE
	factory = '01'
GROUP BY
	CODE
HAVING
	count > 1;
Copy the code

We first obtain the data records of his corresponding factories, then group them according to the codes, and finally obtain the repeated codes by using having filtering on the results of grouping