count.co

Calculations in visuals and low-code cells

Leverage the full power of SQL without the boilerplate

Calculations allow you to perform more complex analyses than possible with the built-in functions available in the visual/low-code UI, without having to write a full SQL statement.

Calculations in Count are SQL expressions, written in a syntax that is heavily influenced by DuckDB (itself similar to PostgreSQL). They can be used with all databases supported by Count and — depending on the database — are parsed and transliterated to the appropriate dialect as required.

Getting started

You can apply calculations to selects, filters and join constraints in visuals and low-code cells. In order to add or edit a calculation, either:

  • Double click on a drop target or select to get an inline-editor

  • Click on a select, filter or join constraint and select Edit calculation... to bring up the calculation modal

Once you're happy with your calculation, submit it using Shift + Enter.

Expressions

Calculations in Count support the following expression types:

  • Literal (0, 0.123, 'abc', NULL, TRUE, FALSE)

  • Identifier (Sales, Orders.Sales)

  • Function (sum(Sales))

  • Between (Sales BETWEEN 10 and 20)

  • In (Region IN ('East', 'West'))

  • Case (CASE WHEN Region = 'East' THEN 0 ELSE 1 END)

  • Cast (CAST(Sales AS VARCHAR))

  • Binary (2 + 2, A OR B)

  • Unary (-2, NOT TRUE)

There's no explicit limit to the length of calculations.

Operators

The precedence of operators (the order in which expressions are evaluated) follows that of PostgreSQL. Parentheses can be used to override this.

Types

The following data types can be used in CAST expressions:

  • VARCHAR ('abc')

  • DOUBLE (1.23)

  • INTEGER (123)

  • DATE (DATE '2024-01-01')

  • TIMESTAMP (TIMESTAMP '2024-01-01T00:00:00')

  • TIMESTAMPTZ (TIMESTAMPTZ '2024-01-01T00:00:00Z')

  • BOOLEAN (TRUE)

Functions

Count supports the following functions (note that some functions may be unavailable on some databases):

Text

Numeric

Date

Intervals take the form of:

  • INTERVAL x <date_part>

  • INTERVAL 'x <date_part> y <date_part> z <date_part> ...'

and the following are valid date parts:

  • YEAR, YR, Y, YEAR, YRS

  • QUARTER, QUARTERS

  • MONTH, MON, MONTHS, MONS

  • WEEK

  • DAY, DAYS, D

  • HOUR, HR, HOURS, HRS, H

  • MINUTES, MIN, MINUTES, MINS, M

  • SECOND, SEC, SECONDS, SECS, S

  • MILLISECONDS, MILLISECOND, MS, MSEC, MSECS, MSECOND, MSECONDS

  • MICROSECONDS, MICROSECONDS, US, USEC, USECOND,

Date part

Aggregate

Window

Utility

PreviousVisual and low-code cellsNextJoins in visuals and low-code cells

Last updated