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)