## 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```

## 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)```

## 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```

## 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'```

## Logical Operators

The logical operators (AND, OR, and NOT) evaluate logical expressions as TRUE, FALSE, or NULL.

Operator: expr AND expr

The following are the possible function return states:
If both expressions are TRUE the function returns to TRUE.
If one or both of the expressions are FALSE the function returns FALSE.
If one expression is FALSE and the other is NULL the function returns FALSE.
If both expressions are NULL the function returns NULL.
If one expression is true and the other is NULL the function returns NULL.
For example:

```SELECT true AND true, false AND true, null AND false, null AND true, null AND null
|
-->  true, false, false, null, null```

Operator: expr OR expr

The following are the possible function return states:
If one or both of the expressions are TRUE the function returns TRUE (including where the other expression is NULL).
If both expressions are FALSE the function returns FALSE.
If both expressions are NULL the function returns NULL.
If one of the expressions is NULL and the other is FALSE the function returns NULL.
For example:

```SELECT true OR false, true OR null, false OR false, null OR null, false OR null
|
-->  true, true, false, null, null```

Operator: NOT expr

The following are the possible function return states:
If the expression is TRUE the function returns FALSE.
If the expression is FALSE the function returns TRUE.
If the expression is NULL the function returns NULL.
For example:

```SELECT 1 NOT in (2,3,4,5,6,7,8,9), NOT(1=1)
|
-->  true, false```