Analytics-Plus Help

MySQL-Aggregate-Functions

The following table lists all the MySQL in-built Aggregate functions supported by ManageEngine Reports. Click on any function to know more about the same.

AVGBIT_ANDBIT_ORBIT_XORCOUNTGROUP_CONCAT
MAXMINSTDSUMVARIANCE

AVG(numeric_arg):

Purpose:

Returns the average of the given term's values.

Example:

Select AVG (col1) from "tab5" returns '3' // Here 'tab5' is the table name and 'col1' is the column name.

BIT_AND(numeric_arg):

Purpose:

The Bitwise AND calculation is performed on the given table's values and the result is returned.

Example:

SELECT BIT_AND (numCol) from " allTypeTest " returns '33' // The Bitwise_AND of the values of 'numcol' rows from the database 'allTypeTest' is returned.

BIT_OR(numeric_arg):

Purpose:

The Bitwise OR calculation is performed on the given table's values and the result is returned.

Example:

SELECT BIT_OR (numCol) from " allTypeTest " returns '127' // The Bitwise_OR of the values of 'numcol' rows from the database 'allTypeTest' is returned.

BIT_XOR(numeric_arg):

Purpose:

The Bitwise XOR calculation is performed on the given table's values and the result is returned.

Example:

SELECT BIT_XOR ( numCol ) from " allTypeTest " returns '121' // The Bitwise_XOR of the values of 'numcol' rows from the database 'allTypeTest' is returned.

Select ATAN ('0.5A@5') returns '0.46364760900081' // As soon as a non numeric character is found in the number string the number's value is returned.

COUNT(numeric_arg):

Purpose:

Counts the number of rows present in the given table.

Example:

Select COUNT (numCol) from " allTypeTest " returns '6' // since 'numcol' column from the database 'allTypeTest' contains 6 rows.

GROUP_CONCAT(numeric_arg):

Purpose:

Concatenates the string values present in the given table.

Example:

Select GROUP_CONCAT ( plainCol ) from "allTypeTest" returns 'Hello,Hlo,Hello,Hlo,Hello,Hlo'

Select GROUP_CONCAT ( DISTINCT plainCol ) from "allTypeTest" returns 'Hello,Hlo' // Does not repeat the output string.

MAX(string_arg, numeric_arg1, numeric_arg2):

Purpose:

The values present in the given columns are compared with each other and the maximum value is returned. The maximum value of both numbers and string are returned.

Example:

Select MAX (col3 ) from "tab2" returns 'sat'

MIN(numeric_arg):

Purpose:

The values present in the given columns are compared with each other and the minimum value is returned. The minimum value of both numbers and string are returned.

Example:

Select MIN (col3 ) from "tab2" returns 'chk'

STD(number1, number2):

Purpose:

The standard deviation of the given table's numeric values are calculated and returned.

Example:

Select STD ( col2 ) from " tab6 " returns '8.0554'

SUM(numeric_arg):

Purpose:

The sum of the given table's values are calculated and returned.

Example:

Select SUM ( col2 ) from " tab6 " returns '32.0'

VARIANCE(numeric_arg):

Purpose:

The variance of the given table's values are calculated and returned.The logarithm of the number to the base 10 is returned.

Example:

Select VARIANCE ( col2 ) from " tab6 " returns '64.8889'

Note :


Share this post : FacebookTwitter