Number Datatype

Heads up! Numbers, Doubles, and Moneys are all numeric types, and can be compared and combined in functions. For example, you could multiply a money value by a number, or compare the values of a number and a double.

Numbers are arbitrary precision, arbitrary scale numbers. They can represent any number exactly, except for numbers whose digits repeat infinitely.

Since Numbers can be either larger or more precise than what doubles allow, many formats, such as JSON, serialize them as strings. For example:

[ {
  "number_column" : "42"
} ]

The following table describes the operators that can be used with Numbers.

Operation Description
< TRUE for numbers less than this one.
<= TRUE for numbers that are less than or equal to this one.
> TRUE for numbers that are greater than this one.
>= TRUE for numbers that are greater than or equal to this one.
!= TRUE for numbers that are not equal to this one.
= TRUE for numbers that are equal to this one.
IS NULL TRUE for numbers that are NULL.
IS NOT NULL TRUE for numbers that are not NULL.
+ Adds two numbers
- Subtracts one number from another
* Multiplies two numbers
/ Divides one number by another
% Returns the modulo of one number divided by another
^ Returns the modulo of one number divided by another

The following table describes the functions that can be used with Numbers.

Function Name Description Availability
avg(...) Returns the average of a given set of numbers 2.0 and 2.1
between ... and ... Returns TRUE for values in a given range 2.1
case(...) Returns different values based on the evaluation of boolean comparisons 2.1
count(...) Returns a count of a given set of records 2.0 and 2.1
in(...) Matches values in a given set of options 2.1
max(...) Returns the maximum of a given set of numbers 2.1
min(...) Returns the minimum of a given set of numbers 2.1
not between ... and ... Returns TRUE for values not in a given range 2.1
not in(...) Matches values not in a given set of options 2.1
stddev_pop(...) Returns the population standard deviation of a given set of numbers 2.1
stddev_samp(...) Returns a sampled standard deviation of a given set of numbers 2.1
sum(...) Returns the sum of a given set of numbers 2.1

For example, to get all of the traffic sensors seeing more than 20,000 vehicles per day from the Chicago Average Daily Traffic Counts:

https://data.cityofchicago.org/resource/u77m-8jgp.json?$where=total_passing_vehicle_volume > 20000

You can also aggregate numbers, so you could also get the average daily count per sensor with avg(...):

https://data.cityofchicago.org/resource/u77m-8jgp.json?$select=avg(total_passing_vehicle_volume)