Documentation Visual SQL (beta)

Filter, Group, and Aggregate Operators in Visual SQL

When you change the grouping or aggregation of a column via its aggregation menu or add a filter in your Query, you’re presented with different operator options depending on the column’s data type. Here are quick explanations for each operator you can use:

Filter operators

Text data types

Operator Description
= equals
!= does not equal
is one of equals one of the values in the specified list
is not one of does not equal one of the values in the specified list
is null does not contain a value
is not null contains a value
like
  • contains the string
  • case sensitive
  • can use SQL wildcards
is not like
  • does not contain the string
  • case sensitive
  • can use SQL wildcards
matches regex matches the specified regular expression
like (case insensitive)
  • contains the string
  • case insensitive
  • can use SQL wildcards
not like (case insensitive)
  • does not contain the string
  • case insensitive
  • can use SQL wildcards

Number data types

Operator Description
= equals
!= does not equal
is one of equals one of the values in the specified list
is not one of does not equal one of the values in the specified list
is null does not contain a value
is not null contains a value
> greater than
>= greater than or equal to
< less than
<= less than or equal to
between and including between the specified range, including the end value

e.g., “between and including 1 and 5” returns “1, 2, 3, 4, 5”

Datetime data types

Operator Description
earlier than before and not including the specified date
later than before and not including the specified date
between between the specified date range, but not including the end date

e.g., “between 2020-06-30 and 2020-07-02” returns 2020-06-30 and 2020-07-01
between and including between the specified date range, including the end date

e.g., “between 2020-06-30 and 2020-07-02” returns dates from 2020-06-30, 2020-07-01, and 2020-07-02
between N and M minutes before now N is the start; M is the end; does not include M

Examples for "between N and M days":

If today is 2020-06-04...

  • "between 0 and 2 days" returns 2020-06-03, 2020-06-04
  • "between 0 and 3 days" returns 2020-06-02, 2020-06-03, 2020-06-04
  • "between 1 and 2 days" returns 2020-06-03
  • "between 1 and 3 days" returns 2020-06-02, 2020-06-03
between N and M hours before now
between N and M days before now
between N and M weeks before now
between N and M months before now
between N and M years before now
current day today
current week this ISO week
current month this month
current quarter this quarter
current year this year
is null does not contain a value
is not null contains a value
last N minutes last specified number of minutes; inclusive
last N hours last specified number of hours; inclusive
last N days last specified number of days; inclusive

For timestamps, subtracts N * 24 hours from the current timestamp
last N weeks last specified number of weeks; inclusive
last N months last specified number of months; inclusive

e.g., If we're in the month of April, "last 2 months" returns February, March, and April
last N years last specified number of years; inclusive

e.g., If we’re in the year 2020, “last 3 years” returns 2017, 2018, 2019, and 2020

Boolean data types

Operator Description
is true A boolean can have either a TRUE or FALSE value, where TRUE = 1 and FALSE = 0.
is false
is not true
is not false
= equals
!= does not equal
is one of equals one of the values in the specified list
is not one of does not equal one of the values in the specified list
is null does not contain a value
is not null contains a value
The operators below are only available when using the Filter Action (see note below table)
> greater than; only shown when using a Filter Action (see note below table)
>= greater than or equal to; only shown when using a Filter Action (see note below table)
< less than; only shown when using a Filter Action (see note below table)
<= less than or equal to; only shown when using a Filter Action (see note below table)

Note: Boolean columns become Number columns post-Query because all TRUE values are shown as 1s and all FALSE values are shown as 0s.


Group operators

Text data types

Operator Description
Group Lists all distinct values
Count of distinct Number of distinct values
Count of all Number of all values, including duplicates
Minimum Sorts the values in alphabetical order and returns the first value
Maximum Sorts the values in alphabetical order and returns the last value
Unaggregated Lists all values, including duplicates

Datetime data types

Operator Description
Second Buckets the time by seconds; only available for datetimes

Format: YYYY-mm-ddTHH:MM:SS

e.g., 2020-06-30T03:20:59
Minute Buckets the time by minutes; only available for datetimes

Format: YYYY-mm-ddTHH:MM

e.g., 2020-06-30T03:20
Hour Buckets the time by hours; only available for datetimes

Format: YYYY-mm-ddTHH

e.g., 2020-06-30T03
Day Buckets the time by days

Format: YYYY-mm-dd

e.g., 2020-06-30T03:20
Week Buckets the time by weeks

Format: YYYY-W(01-52)

e.g., 2020-W22
Month Buckets the time by months

Format: YYYY-mm

e.g., 2020-06
Quarter Buckets the time by quarters

Format: YYYY-Q(1-4)

e.g., 2020-Q2
Year Buckets the time by years

Format: YYYY

e.g., 2020
Hour of Day 00 to 24; only available for datetimes
Day of Week Sunday, Monday, Tuesday, …, or Saturday
Day of Month 01 to 31
Day of Year 001 to 366
Month of Year January, February, March, …, December
Count of distinct Number of distinct datetime values
Count of all Number of all datetime values, including duplicates
Minimum The earliest datetime value
Maximum The latest datetime value
Group Lists all distinct datetime values
Unaggregated Lists all datetime values, including duplicates

Boolean data types

Operator Description
Group Lists all distinct values
Count of distinct Number of distinct values
Count of all Number of all values, including duplicates
Unaggregated Lists all values, including duplicates

Aggregate operators

Number data types

Operator Description
Count of distinct Number of distinct values
Count of all Number of all values, including duplicates
Total sum Sums all values
Average Takes average of all values
Minimum The smallest value
Maximum The largest value
Group Lists all distinct values
Unaggregated Lists all values, including duplicates