MySQL GROUP by group query

Take a look at the following table:

Problem: The number of hd100 in the service domain (service_domain_moid) A and the number of hd100s are calculated with the same statistic time (statistic) .

How to use: Group by

First, let's filter service_domain_moid to a, as follows:

SELECT * from statistic where service_domain_moid= "a";

Second: We divide the statistical time (Statistic_time) into the same group, summing

Select SUM (hd100), sum (hd100s) from statistic where service_domain_moid= ' a ' group by Statistic_time;

Finally, the statistical results are sorted in descending order:

sort the results of a query :

the data that is queried from the table may be unordered or not in the order in which the user expects it to be arranged. To enable the order of the query results to meet the needs of the user, the ORDER by keyword can be used to sort the records. The syntax rules are as follows:

ORDER by property name [asc| DESC]

where the attribute name parameter means that the field is sorted by; The ASC parameter indicates sorting in ascending order,and the DESC parameter means sorting in descending order. By default, the order is sorted by ASC.

Group query:

The group by keyword can group query results by a field or multiple fields. The values in the field are equal to one group. The syntax rules are as follows:

GROUP by property name [having conditional expression] [with ROLLUP]

where "attribute name" means grouping according to the value of the field; The "Having conditional expression " is used to restrict the display after grouping, and the result of satisfying the conditional expression will be displayed; the WITH ROLLUP keyword will add a record at the end of all records. The record is the sum of all the records above.

The GROUP by keyword can be used with the Group_concat () function. the Group_concat () function displays the value of the specified field in each group. Also, the GROUP by keyword is typically used with aggregate functions. The aggregate functions include COUNT (), SUM (), AVG (), MAX (), and MIN (). count () is usedto count the number of records, SUM () is used to calculate the sum of the values of the field; AVG () is used to calculate the average of the field;Max () is used to query the maximum valueof the field; MIN () is used to query the minimum value of a field.

When the group by keyword is associated with the Group_concat () function, the value of the field specified in each group is displayed.

When the group by keyword is used with a collection function, the aggregate record, maximum value, minimum value, and so on, can be computed from the collection function.

The GROUP by keyword and having, if you add a "have conditional expression ", You can limit the output results. The result of the conditional expression is displayed if it is met.

Description: Both the having conditional expression and the WHERE conditional expression are used to limit the display. However, the two work in different places. a WHERE condition expression acts on a table or view, which is the condition of a table and a view. A having conditional expression acts on a grouped record to select a group that satisfies a condition.

The GROUP by keyword is used with the with ROLLUP :

Use With ROLLUP , a record is added at the end of all records. This record is the sum of all the records above.

MySQL GROUP by group query