The grouping and analysis of database table entries is of practical interest in many applications. Solving this kind of tasks with MySQL tools allows you to perform large amounts of routine work quickly and efficiently.
However, it should be borne in mind: "purity" of the result will depend on many factors. The MySQL group by construct is executed as written by the programmer, but its algorithm may depend on the query execution time and the sampling frequency.
The syntax of a grouping operation in a query
When compiling a grouping request, it is essential what exactly is selected and how it is grouped. The source table contains a small number of fields, each of which has a different number of unique records. The fields i_status and w_status are equivalent: the first is the position code, the second is its name. The field of record number and time of record creation do not have special meaning.
You can perform group operations on such a set, for example, determine the maximum and minimum value of the start_timestamp field.
Similarly, you can formulate a group request and determine all the posts and the number of people employed in them.
The MySQL: group by & order by relationship is traced on queries (i) and (iv), queries (ii) and (iii) are equivalent. But in any particular situation, you should consider how to sort when performing the grouping operation. In some cases this can be a problem.
Counting the number of grouped records is an important point when grouping. This operation is mainly used precisely for these purposes, although its use to define various rows in a table is somewhat more convenient, in contrast to the distinct construction.
Grouping by volume of occurrence
It should be noted that MySQL group by will not always work against MySQL order by. The question is not so much in the priorities of the constructs as in the logic of the request itself.
The request can be formulated as an expression. In the simplest case, the expression may consist in counting the quantity. That is, the application in MySQL group by, count (*) - as an expression by which grouping is performed.
Here, ascending sorting is the same as that specified in order by.
The MySQL language does not limit the programmer in constructing expressions, indicating fields that do not participate in grouping, and the sequence of grouped elements. But MySQL group by may not work as the programmer thinks is right.
Multiple grouping
You can group anything and anything, but you should always strive to minimize the number of fields used.
MySQL group by is a very convenient operation on duplicate data, it can easily be used to obtain the necessary information, but if several fields are grouped in a query, it is advisable to consider other options for building the query.
It should be taken into account that the group by and order by constructions are considered by the MySQL language as simple “arithmetic” over records. For responsible grouping operations, options for built-in queries, the reasonable use of keys, joining and intersecting tables are intended.
Grouping in the context of MySQL group by is a quick way to get generalized information; you should not load queries with this construction with a deeper meaning. This can lead to unpredictable results and serious waste of time.