Mysql group by function tutorial

This chapter describes the group by (aggregate) functions used for a group of numerical operations. Unless otherwise specified, the group function ignores NULL values. If you use a group function in a statement that does not contain the roup by clause, it is equivalent to grouping all rows.

Group by (aggregate) Function
This chapter describes the group (SET) functions used for a set of numeric operations. Unless otherwise specified, the group function ignores NULL values.

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

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

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.cn
Returns bitwise AND of all BITs in expr. The computing accuracy is 64-bit (BIGINT ).

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

BIT_OR (expr)
Returns bitwise OR of all BITs in expr. The computing accuracy is 64-bit (BIGINT ).

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

BIT_XOR (expr)
Returns bitwise XOR of all BITs in expr. The computing accuracy is 64-bit (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;

 

The slight difference between COUNT (*) is that it returns the number of retrieved rows, whether or not it contains NULL values.

When SELECT is retrieved from a table, but no other columns are retrieved, and there is no WHERE clause, COUNT (*) is optimized to the fastest return speed. For example:

Mysql> select count (*) FROM student;

This optimization only applies to MyISAM tables because these table types store the exact number of records returned by a function and are very easy to access. For transaction-type storage engines (InnoDB and BDB), there are many problems with storing a precise number of rows, because multiple transactions may be processed, and each row may have an impact on the number of rows.

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

If no matching item is found, COUNT (DISTINCT) returns 0.

Mysql> select count (DISTINCT results) FROM student;

In MySQL, you obtain the number of expressions that do not contain NULL by specifying an expression list. In standard SQL, you must connect all expressions in COUNT (DISTINCT.

GROUP_CONCAT (expr)
This function returns a string with a non-NULL value from a group of connections. The 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 obtain the join value of expression combinations. You can use DISTINCT to delete duplicate values. If you want to sort multiple result values, use the order by clause. To sort in reverse ORDER, add the DESC (descending) keyword to the column name that you want to sort using the order by clause. The default order is ascending. You can use ASC to specify it. SEPARATOR follows the string value in the middle of the value of the inserted result. The default value is comma (','). You can delete all separators by specifying the SEPARATOR.

Using group_concat_max_len system variables, you can set the maximum allowed length. The syntax for this operation in the 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 ended with the maximum length.

MIN ([DISTINCT] expr), MAX ([DISTINCT] expr)
Returns the minimum and maximum values of expr. Values of MIN () and MAX () can be a string parameter; in these cases, they return the minimum or maximum string value. See section 7.4.5, "How to Use indexes for MySQL ". The DISTINCT keyword can be used to find the minimum or maximum values of different expr values. However, the result is the same as that of omitted DISTINCT.

If no matching row is 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 SET functions, MySQL compares the ENUM and SET columns according to their string values instead of the relevant positions of strings in the SET. This is different from the order by method. This should be improved in the 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. The standard SQL function STDDEV_POP () can be used instead.

If no matching row is found, these functions return NULL.

STDDEV_POP (expr)
Returns the total standard deviation of expr (square root of VAR_POP ). You can also use STD () or STDDEV (). They have the same meaning, but they are not standard SQL.

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

STDDEV_SAMP (expr)
Returns the sample standard deviation of expr (square root of VAR_SAMP ).

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

SUM ([DISTINCT] expr)
Returns the total number of expr instances. If no row exists in the returned set, SUM () returns NULL. The DISTINCT keyword can be used in MySQL 5.1 to obtain the sum of different expr values.

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

VAR_POP (expr)
Returns the population standard variance of expr. It regards the row as a population rather than a sample, so it regards the number of rows as the denominator. You can also use VARIANCE (), which has the same meaning but is not a standard SQL statement.

If no matching item is found, VAR_POP () returns NULL.

VAR_SAMP (expr)
Returns the sample variance of expr. More specifically, 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. The standard SQL function VAR_POP () can be used instead.

If no matching item is found, VARIANCE () returns NULL.