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: |
|---|
| + |
| - |
| * |
| / |
| ^ |
| % |
| abs |
| acos |
| asin |
| atan |
| cbrt |
| ceil |
| cos |
| cosh |
| floor |
| sin |
| sqrt |
| tan |
STANDARD DEVIATION
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)