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.

    AVG BIT_AND BIT_OR BIT_XOR COUNT GROUP_CONCAT
    MAX MIN STD SUM VARIANCE

    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.

    Note :

    • Returns null if the column does not contain any values.
    • Returns '0.0' if a non-numeric character occurs in the values.

    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.

    Note :

    • Returns null if the column does not contain any values.
    • Returns '0' if a non-numeric character occurs in the values.

    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.

    Note :

    • Returns null if the column does not contain any values.
    • Returns '0' if a non-numeric character occurs in the values.

    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.

    Note :

    • Returns null if the column does not contain any values.
    • Returns '0' if a non-numeric character occurs in the values.

    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.

    Note :

    • Returns '0' if the column does not contain any values.
    • The values can be both numbers and string.

    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.

    Note :

    • Returns null if the column does not contain any values.

    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'

    Note :

    • Returns null if the column does not contain any values.

    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'

    Note :

    • Returns null if the column does not contain any values.

    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'

    Note :

    • Returns null if the column does not contain any values.
    • Returns '0.0' if the column contains only non-numeric character values.

    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'

    Note :

    • Returns null if the column does not contain any values.
    • Returns '0.0' if the column contains only non-numeric character values.

    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 :

    • Returns null if the argument is null, negative or any non-numeric character.
    • Returns '0.0' if the column contains only non-numeric character values.
     

    Copyright © 2016, ZOHO Corp. All Rights Reserved.