MySQL GROUP by function tutorial

GROUP by (aggregate) functions
This chapter discusses group (set) functions for a set of numeric operations. Unless otherwise stated, the group function ignores NULL values.

If you use a group function in a statement that does not contain a roup by clause, it is equivalent to grouping all rows.

AVG ([DISTINCT] expr)
Returns the average of expr. The DISTINCT option can be used to return the average value of the different values of expr.

If no matching row is found, AVG () returns NULL.

Mysql> SELECT Student_name, AVG (Test_score)

-> from Student

-> GROUP by Student_name;

Bit_and (expr) www.111cn.net
Returns the bitwise AND of all bits in expr. The calculated execution is 64 bits (BIGINT).

If no matching row is found, the function returns 18446744073709551615. (This is an unsigned BIGINT value, all bits are set to 1).

Bit_or (expr)
Returns the bitwise OR of all bits in expr. The calculated execution is 64 bits (BIGINT).

If no matching row is found, the function returns 0.

Bit_xor (expr)
Returns the bitwise XOR of all bits in expr. The calculated execution is 64 bits (BIGINT).

If no matching row is found, the function returns 0.

COUNT (expr)
Returns the number of non-null values in the row retrieved by the SELECT statement.

If no matching row is found, count () returns 0.

Mysql> SELECT Student.student_name,count (*)

-> from Student,course

-> WHERE student.student_id=course.student_id

-> GROUP by Student_name;

A slightly different point of COUNT (*) is that it returns the number of rows retrieved, regardless of whether they contain null values.

SELECT retrieves from one table without retrieving other columns, and when there is no WHERE clause, COUNT (*) is optimized to the fastest return speed. For example:

Mysql> SELECT COUNT (*) from student;

This optimization applies only to MyISAM tables, because these table types store a function that returns the exact number of records and is very easy to access. For a transaction-type storage engine (InnoDB, BDB), there are more problems storing an exact number of rows, because multiple things can happen, and each can have an impact on the number of rows.

COUNT (DISTINCT expr,[expr ...])
Returns the number of different non-null values.

If no matching items are found, COUNT (DISTINCT) returns 0.

Mysql> SELECT COUNT (DISTINCT results) from student;

In MySQL, you get the number of different combinations of expressions that do not contain null, given an expression list. In standard SQL, you will have to be in count (DISTINCT ...) All expressions are connected in the

Group_concat (expr)
This function returns a string result with a non-null value of a connection from a group. Its complete syntax is as follows:

Group_concat ([DISTINCT] expr [, expr ...]

[ORDER BY {Unsigned_integer | col_name | expr}

[ASC | DESC] [, col_name ...]]

[SEPARATOR Str_val])

Mysql> SELECT Student_name,

-> Group_concat (Test_score)

-> from Student

-> GROUP by Student_name;

Or:

Mysql> SELECT Student_name,

-> Group_concat (DISTINCT test_score

-> ORDER by Test_score DESC SEPARATOR ')

-> from Student

-> GROUP by Student_name;

In MySQL, you can get the join value of an expression combination. You can use distinct to delete duplicate values. If you want multiple result values to be sorted, you should use the ORDER BY clause. To be in reverse order, add the DESC (descending) keyword to the name of the column you want to sort by the ORDER BY clause.   The default order is ascending, and you can use ASC to specify it explicitly. SEPARATOR follows the string value in the middle of the value that should be inserted into the result. The default is comma (', '). By specifying separator ', you can remove all delimiters.

Using the Group_concat_max_len system variable, you can set the maximum allowable length. The syntax for doing this in a program is as follows, where Val is an unsigned integer:

SET [Session | GLOBAL] Group_concat_max_len = val;

If the maximum length has been set, the result is the maximum length.

MIN ([DISTINCT] expr), MAX ([DISTINCT] expr)
Returns the minimum and maximum values for expr. The min () and Max () values can be a string parameter, in which case they return the minimum or maximum string value.  See section 7.4.5, "How MySQL uses indexes." Distinct keywords can be used to find the minimum or maximum value of the different values of expr, however, this produces the same result as omitting the distinct.

If no matching rows are found, MIN () and Max () return NULL.

Mysql> SELECT Student_name, MIN (Test_score), MAX (Test_score)

-> from Student

-> GROUP by Student_name;

For min (), MAX (), and other aggregate functions, MySQL is currently comparing the enum and set columns according to their string value rather than the string in the collection at its relative position. This differs from the way the order is compared. This should be improved in a future version of MySQL.

STD (expr) StdDev (expr)
Returns the overall standard deviation of expr. This is an extension of standard SQL. The StdDev () Form of this function is used to provide compatibility with Oracle. You can use the standard SQL function Stddev_pop () instead.

If no matching rows are found, the functions return NULL.

Stddev_pop (expr)
Returns the total standard deviation of expr (the square root of the Var_pop ()). You can also use STD () or StdDev (), which have the same meaning, but are not standard SQL.

If no matching row is found, Stddev_pop () returns NULL.

Stddev_samp (expr)
Returns the sample standard deviation of expr (the square root of the Var_samp ()).

If no matching row is found, Stddev_samp () returns NULL.

SUM ([DISTINCT] expr)
Returns the total number of expr. If no rows are returned in the collection, SUM () returns NULL. DISTINCT keywords can be used in MySQL 5.1 to get the sum of the different values of expr.

If no matching row is found, sum () returns NULL.

Var_pop (expr)
Returns the overall standard variance of expr. It treats the row as a whole, not a sample, so it takes the number of rows as the denominator. You can also use variance (), which has the same meaning but not the standard SQL.

If no matching entry is found, Var_pop () returns NULL.

Var_samp (expr)
Returns the sample variance of expr. More precisely, the denominator number is the number of rows minus 1.

If no matching row is found, var_samp () returns NULL.

Variance (expr)
Returns the population standard variance of expr. This is an extension of standard SQL. You can use the standard SQL function Var_pop () instead.

If no matching entry is found, variance () returns NULL.