Arithmetic Functions

Function: abs(numeric_expr)

This function returns the absolute value of the numeric expression.

SELECT abs(5 - 7), abs(-2), abs(2)
|
-->  2, 2, 2

Function: ceil(numeric_expr)

This function returns the numeric expression rounded up to nearest whole number.

SELECT ceil(13 / 2), ceil(6.5)
|
--> 7, 7

Function: floor(numeric_expr)

This function returns the numeric expression rounded down to the nearest whole number.

SELECT floor(13 / 2), floor(6.5)
|
--> 6, 6

Function: pow(numeric_expr1, numeric_expr2)

This function raises the first numeric expression to the power of the second numeric expression and returns the result.

SELECT pow(3 , 2), pow(3 , -2)
|
--> 9, 0.11111111111

Function: round(numeric_expr)

This function returns the numeric expression rounded up or down to the nearest whole number.

SELECT round(6.5), round(6.49), round(6.51)
|
--> 7, 6, 7

Function: sqrt(numeric_expr)

This function returns the square root of the numeric expression.

SELECT sqrt(36)
|
--> 6
Print Friendly, PDF & Email

Arithmetic Operators

Operator: numeric_expr + numeric_expr

This operator performs the addition operation and returns the sum of the two numeric expressions.

SELECT (12+5)

-->  17

Operator: numeric_expr – numeric_expr

This operator performs the subtraction operation and returns the difference of the two numeric expressions. This operator can also be used to turn a given value into a negative value.

SELECT (17 - 5)

-->  12

Operator: numeric_expr * numeric_expr

This operator performs the multiplication of two expressions and returns the product.

SELECT (2*6)

-->  12

Operator: numeric_expr / numeric_expr

This operator performs the division of the first expression by the second expression and returns the quotient.

SELECT (12 / 6)

-->  2

Operator: numeric_expr % numeric_expr

This is the modulo operator. It returns the value of the remainder of a division operation.

SELECT (13 % 6)

--> 1
Print Friendly, PDF & Email

Aggregation Functions

An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria, to form a single value of more significant meaning or measurement.

Function: avg(numeric_expr)

This function returns the average of the values for a group of rows computed by numeric_exp. NULL value rows are not included in the calculation.

SELECT customer_user_id, avg(amount_spent) as avg_spent
FROM cooladata
WHERE date_range(all)
GROUP BY customer_user_id

Function: count(*)

This function returns the total number of values (including NULLs) in the scope of the function.

SELECT count(*)
FROM cooladata
WHERE date_range(all)

Function: count([DISTINCT field [, exact])

This function returns the total number of non-NULL values in the scope of the function. If you use the DISTINCT keyword, the function returns the number of unique values in the specified field. If the “exact” argument is requested, exact computation will occur and no estimation will take place.

SELECT count(distinct user_id)
FROM cooladata
WHERE date_range(all)

Function: stddev(numeric_expr)

This function returns the standard deviation of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.

SELECT stddev(amont_spent) as stddev
FROM cooladata
WHERE date_range(all)

Function: variance(numeric_expr)

This function returns the variance of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.

SELECT variance(amount_spent) as spent_variance
FROM cooladata
WHERE date_range(all)

Function: max(field)

This function returns the maximum value in the scope of the function.

SELECT customer_user_id, max(amount_spent) as max_spent
FROM cooladata
WHERE year(event_time) = 2013
GROUP BY customer_user_id
ORDER BY spent DESC
LIMIT 100

Function: min(field)

This function returns the minimum value in the scope of the function.

SELECT customer_user_id, datediff(max(event_time_ts),min(event_time_ts)) as engagement_duration
FROM cooladata
WHERE date_range(all)
GROUP BY customer_user_id
ORDER BY engagement_duration
LIMIT 100

Function: sum(field)

This function returns the sum total of the values in the scope of the function. Only numeric data typed fields may be used with this function.

SELECT count(session_id) AS session_count, sum(amount_spent) as revenue
FROM cooladata
WHERE date_range(all)
Print Friendly, PDF & Email

Comparison Functions

Comparison functions return TRUE or FALSE.

Comparison functions are used as arguments with either numeric or string expressions. String constants must be enclosed in single or double quotes. The expressions can be literal or dynamically fetched by a query. Comparison functions can be used in most query clauses, but are most often used as filtering conditions in WHERE clauses.

Function: expr1 = expr2

This function checks for equality, it returns TRUE if the two expressions are equal, and FALSE if not.

SELECT 1=2, 'q'='q'
|
-->  false, true

Function: expr1 != expr2 | expr1 <> expr2

These functions check for inequality. They return TRUE if the two expressions are not equal, and FALSE if they are.

SELECT 1!=2, 'q'<>'q'
|
-->  true, false

Function: expr1 > expr2

This function checks that the expression on the left is greater than the one on the right. The function returns TRUE if the left side is greater than the right side, and FALSE if not.

SELECT 1 > 2, 2 > 1
|
-->  false, true

Function: expr1 < expr2

This function checks that the expression on the left is less than the expression on the right. The function returns TRUE if the left side is less than the right side, and FALSE if not.

SELECT 1 < 2, 2 < 1
|
-->  true, false

Function: expr1 >= expr2

This function checks that the expression on the left is greater than or equal to the one on the right. The function returns TRUE if the left side is greater than or equal to the right side, and FALSE if not.

SELECT 1 >= 1, 1 >= 2, 1 >= 0
|
-->  true, false, true

Function: expr1 <= expr2

This function checks that the expression on the left is less than or equal to the one on the right. The function returns TRUE if the left side is less than or equal to the right side, and FALSE if not.

SELECT 1 <= 1, 1 <= 2, 1 <= 0
|
-->  true, true, false

Function: expr IS NULL

This function checks if the expression evaluates to NULL. It returns TRUE if NULL, and FALSE if not.

SELECT 1 is NULL, 1 is not NULL
|
-->  false, true

Function: expr IN(expr1, expr2, …)

This function checks if the expression value is found in a list of values. If it is found, it returns TRUE, otherwise it returns FALSE. The list must contain constants or literals. In order to evaluate correctly, it must be the same data type as the expression.

SELECT 'adam' in ('ruth', 'bill', 'adam'), 'bob' in ('ruth', 'bill', 'adam'), 1 in (1,2,3,4,5,6,7,8,9)
|
-->  true, false, true

 

Function: if(condition, true_return, false_return)

This function returns either val_true or val_false, depending on whether the condition is true or false. The return values can be literals or field-derived values, but they must be the same data type. Field-derived values do not need to be included in the SELECT clause.

SELECT if(user is not NULL, user, 'Unknown user')

 

Function: IFNULL(expr, null_default)

This function offers a way to handle NULLs. If the expression is not NULL it is returned, if it is NULL then the value in null_default is returned. The expression and the default value must be the same type.

SELECT ifnull( null, true), ifnull(1,999)
|
-->  true, 1
Print Friendly, PDF & Email

Conversion Functions

Conversion functions are used to change the data type of a numeric expression. Conversion functions should be used to ensure that arguments in a comparison function have the same data type.

Function: boolean(numeric_expr)

This function returns:
TRUE if numeric_expr is not 0 and not NULL.
FALSE if numeric_expr is 0.
NULL if numeric_expr is NULL.
For example:

SELECT boolean(1), boolean(87.35), boolean(0), boolean(null)
|
-->  true, true, false, null

Function: float(expr)

This function returns the argument as a double. The argument can be a string, such as ‘45.78’. The function returns NULL for non-numeric values.

SELECT float(1), float('87.35'), float('david')
|
-->  1.0, 87.35, null

Function: integer(expr)

This function returns the argument as a 64-bit integer. The function accepts:
Strings such as ‘45’.
Hexadecimals such as ‘0x2D’.
Octals such as ‘055’.
The function returns NULL for non-integer values.

SELECT integer(1.5), integer('1.5'), integer('87'), integer('088')
|
-->  1, null, 87, 88

Function: string(numeric_expr)

This function returns the numeric argument as a string.

SELECT string(1.5), string(45)
|
-->  '1.5', '45'
Print Friendly, PDF & Email