NUMBER_FORMAT
This function allows you to control the display of leading and trailing zeros, prefixes and suffixes, grouping (thousands) separators, and the decimal separator.
NUMBER_FORMAT(<number>,<format>)
select number_format(clicks,##,###.00) as Number of clicks
The output for the preceding lines of code is described in the following table. The value is the number, a double, that is to be formatted. The pattern is the String that specifies the formatting properties. The output, which is a String, represents the formatted number:
| value | pattern | output | explanation |
|---|---|---|---|
| 123456.789 | ###,###.### | 123,456.789 | The pound sign (#) denotes a digit, the comma is a placeholder for the grouping separator, and the period is a placeholder for the decimal separator. |
| 123456.789 | ###.## | 123456.79 | The value has three digits to the right of the decimal point, but the pattern has only two. The format method handles this by rounding up. |
| 123.78 | 000000.000 | 000123.780 | The pattern specifies leading and trailing zeros because the 0 character is used instead of the pound sign (#). |
| 12345.67 | $###,###.### | $12,345.67 | The first character in the pattern is the dollar sign ($). Note that it immediately precedes the leftmost digit in the formatted output. |
| 12345.67 | \u00A5###,###.### | ¥12,345.67 | The pattern specifies the currency sign for Japanese yen (¥) with the Unicode value 00A5. |
Note: the pattern string stands for a digit, so zero will show as absent
Arithmetic Operations
Arithmetic operations can be used within the query
select (opened/sent)*100 as Open Rate, Customer
|
Supported operators |
Function |
|---|---|
| + | Add |
| - | Subtract |
| * | Multiply |
| / | Divide |
| ^ | Power of |
| % | Modulus |
| abs | Absolute |
| acos | Inverse cosine |
| asin | Inverse sine |
| atan | Inverse tan |
| cbrt | Cube root |
| ceil | Ceil |
| cos | Cosine |
| cosh | Hyperbolic cosine |
| floor | Floor |
| sin | Sine |
| sqrt | Square root |
| tan | Tangent |
Standard Deviation
| Function | Description | Example |
|---|---|---|
| + | Adds numeric values | dailyScore + 250 |
| - | Subtracts numeric values | 100 - avgPrive |
| * | Multiplies numeric values | 42 * ttlClicks * 2 |
| / | Divides numeric values | 50 / 10 |
| ^ | Raises value to the power of exponent | 7 ^ 7 |
| % | Returns the remainder left over when one operand is divided by a second operand |
maxSpeed % 3 |
| abs | Returns the absolute value |
abs(-6) |
| acos | Returns the inverse cosine of a value |
acos(value) |
| asin | Returns the inverse sine of a value |
asin(value) |
| atan | Returns the inverse tangent of a value | atan(value) |
| cbrt | Returns the cube root | cbrt(value) |
| ceil | Returns the next integer if greater than 0.5 | ceil(value) |
| cos | Returns the cosine of a value | cos(value) |
| cosh | Returns the hyperbolic cosine function | cosh(value) |
| floor | Returns the integer if lesser than 0.5 | floor(value) |
| sin | Returns the sine of a value | sin(value) |
| sqrt | Returns the square root | sqrt(value) |
| tan | Returns the tangent of a value | tan(value) |
Useful to determine variance of a set of values;
select sd(opened) as Std Deviation, Customer group by customer
ROUND
Specify the number of decimal points to display
ROUND(<field>, <decimal points>)
select round(sent,1)
PERCENTILE
Returns the value of the field for the specified percentile rank.
PERCENTILE(<field>, <percentile>)
select percentile(sent,75)