SQL formula reference — recipes and per-database syntax

Do it

1. Recipe — Safe divide (divide-by-zero protection)

   The problem. SQL throws a divide-by-zero error the moment your divisor hits 0. One bad row, and the whole report breaks.

   The fix. Wrap the divisor in NULLIF(..., 0) — converts 0 to NULL, and dividing by NULL returns NULL instead of erroring. The reporting row just shows a blank instead of crashing the query.

   ? / NULLIF([alias].[divisor], 0)

   Per-DB note. Identical syntax across MSSQL, PostgreSQL, and MySQL — NULLIF is one of the genuinely portable functions. Set Data Type to num (it returns a numeric result, possibly NULL).

   If you'd rather show 0 instead of NULL on the bad rows, wrap the whole thing in COALESCE: COALESCE(? / NULLIF([alias].[divisor], 0), 0).

2. Recipe — Integer-to-decimal coercion

   The problem. When both operands of a division are integers, most databases return an integer result. 5 / 2 gives 2, not 2.5. Your "percentage of total" report shows 0% for everything that isn't a clean 100%.

   The fix. Cast at least one operand to a decimal type before the math. Syntax differs per DB:

   Database

   Cast expression

   MSSQL

   CAST(? AS DECIMAL(18,4))

   PostgreSQL

   ?::numeric or CAST(? AS NUMERIC)

   MySQL

   CAST(? AS DECIMAL(18,4))

   Set Data Type to num. If your displayed numbers need to be tidied, wrap with ROUND(..., 2) for two decimal places — see the next recipe for the combined pattern.

3. Recipe — Percentage calculation (safe divide + casting + rounding)

   The three previous patterns combined. Use this for any "X as a percent of Y" calculation — % of total, completion rate, conversion rate, anything ratio-shaped.

   MSSQL / MySQL:

   ROUND( CAST(? AS DECIMAL(18,4)) / NULLIF([alias].[total], 0) * 100, 2 )

   PostgreSQL:

   ROUND( ?::numeric / NULLIF([alias].[total], 0) * 100, 2 )

   Set Data Type to num if you want the percent sign managed at the viz layer, or per if you want DashboardFox to render the % automatically. The per data type expects a value already expressed as a percent (75.5, not 0.755) — the * 100 at the end of the formula gets you there.

4. Recipe — NULL-safe CONCAT (full name from first + last)

   The problem. If any input to CONCAT is NULL, some databases return NULL for the whole expression (or behave inconsistently depending on session settings). A customer with no middle name ends up with no name at all.

   The fix. Wrap each potentially-null input in COALESCE(..., '') — converts NULL to empty string before the concatenation.

   CONCAT( COALESCE(?, ''), ' ', COALESCE([alias].[last_name], '') )

   Per-DB note. CONCAT with multiple arguments works on MSSQL 2012+, PostgreSQL, and MySQL. COALESCE is universal. Set Data Type to cha.

   For three or more parts (first + middle + last, or street + city + state + zip), keep extending the same pattern. Each piece gets its own COALESCE; literal separators (spaces, commas) sit between as plain strings.

5. Recipe — Multi-branch CASE bucketing

   Roll up many raw values into a smaller set of categories. The pattern from report-formulas generalized — match values into groups, with an ELSE catch-all for whatever doesn't match.

   CASE WHEN ? IN ('Shoplite Grocery', 'Genesis Groceries', 'FreshMart') THEN 'Grocery' WHEN ? IN ('Cafe X', 'Coffee Y') THEN 'Cafe' WHEN ? LIKE '%Restaurant%' OR ? LIKE '%Diner%' THEN 'Restaurant' ELSE 'Other' END

   Three things worth noting:

   • Order matters. The first matching WHEN wins, so put more-specific conditions before catch-alls. A row matching both Cafe and Restaurant would resolve to whichever WHEN comes first.
   • Mix operators. IN, LIKE, comparison operators, and BETWEEN all work inside CASE conditions. Combine with AND / OR as needed.
   • Numeric buckets work the same. Score ranges → letter grades, ages → age groups, anything where you want to fold a continuum into named bins.

   Set Data Type to cha (the output is text). Identical syntax across MSSQL, PostgreSQL, and MySQL — CASE is one of the most portable SQL constructs.

6. Reference — Aggregate functions

   Aggregates collapse many rows into one summary value. Used in every "count by," "sum by," or "average by" report. Always set Data Type to num.

   Function

   What it does

   Expression

   Per-DB variants

   COUNT

   Number of non-null rows

   COUNT(?)

   —

   COUNT DISTINCT

   Unique values

   COUNT(DISTINCT ?)

   —

   SUM

   Total of numeric values, ignoring NULLs

   SUM(?)

   —

   AVG

   Mean of numeric values, ignoring NULLs

   AVG(?)

   —

   MIN

   Smallest value

   MIN(?)

   —

   MAX

   Largest value

   MAX(?)

   —

   Aggregates work cleanly across all three databases. The non-visible-column pattern from report-formulas is the most common way to apply them.

7. Reference — String functions

   Operate on text columns. Output is text (cha) unless noted otherwise. LENGTH returns a number.

   Function

   What it does

   Default expression

   Per-DB variants

   UPPER

   Convert to uppercase

   UPPER(?)

   —

   LOWER

   Convert to lowercase

   LOWER(?)

   —

   LENGTH

   Number of characters (returns num)

   LENGTH(?)

   MSSQL: LEN(?) · MySQL: CHAR_LENGTH(?)

   TRIM

   Remove leading/trailing blanks

   TRIM(?)

   MSSQL pre-2017: LTRIM(RTRIM(?))

   LEFT

   Leftmost N characters

   LEFT(?, n)

   —

   RIGHT

   Rightmost N characters

   RIGHT(?, n)

   —

   SUBSTRING

   Slice from position, length N

   SUBSTRING(?, start_position, number_of_chars)

   MySQL also: SUBSTR(?, start_position, number_of_chars)

   REPLACE

   Replace all occurrences of a substring

   REPLACE(?, 'old', 'new')

   —

   CONCAT

   Join strings together

   CONCAT(?, [insert_field])

   Postgres also: ? || [insert_field]

   POSITION

   Position of substring (returns num; 0 if not found)

   POSITION('text' IN ?)

   MSSQL: CHARINDEX('text', ?) · MySQL: INSTR(?, 'text')

   The TRIM row deserves a note: MSSQL only added TRIM(?) in 2017. On MSSQL 2016 or earlier, use LTRIM(RTRIM(?)) — works on every MSSQL version including current. If you're not sure what version your DB is on, the LTRIM(RTRIM()) form is the safe default.

8. Reference — DateTime functions

   Date extraction and arithmetic. The function-type picker in Composer fills in the right syntax for your DB, but the table below is what you'll want when writing by hand or working with AI.

   Need

   Description

   MSSQL

   PostgreSQL / MySQL

   Current timestamp

   Right now, with time

   GETDATE()

   PG: NOW() · MySQL: NOW()

   Current date only

   Today, no time

   CAST(GETDATE() AS DATE)

   PG: CURRENT_DATE · MySQL: CURDATE()

   Year

   Numeric year (num)

   YEAR(?) or DATEPART(year, ?)

   PG: EXTRACT(YEAR FROM ?) · MySQL: YEAR(?)

   Month number

   1–12 (num)

   MONTH(?) or DATEPART(month, ?)

   PG: EXTRACT(MONTH FROM ?) · MySQL: MONTH(?)

   Month name

   "January" (cha)

   DATENAME(month, ?)

   PG: TO_CHAR(?, 'Month') · MySQL: MONTHNAME(?)

   Day of month

   1–31 (num)

   DAY(?) or DATEPART(day, ?)

   PG: EXTRACT(DAY FROM ?) · MySQL: DAY(?)

   Day of week (name)

   "Monday" (cha)

   DATENAME(weekday, ?)

   PG: TO_CHAR(?, 'Day') · MySQL: DAYNAME(?)

   Hour

   0–23 (num)

   DATEPART(hour, ?)

   PG: EXTRACT(HOUR FROM ?) · MySQL: HOUR(?)

   Difference (days)

   Days between two dates (num)

   DATEDIFF(day, [alias].[start], [alias].[end])

   PG: [end] - [start] · MySQL: DATEDIFF([end], [start])

   Add interval

   Date plus N days

   DATEADD(day, n, ?)

   PG: ? + INTERVAL 'n days' · MySQL: DATE_ADD(?, INTERVAL n DAY)

   Two extraction patterns recur often enough to flag: month name sorts alphabetically (Apr, Aug, Dec…) unless you also bring in the month-number column and sort by that, then mark month-number non-visible. The same trick applies to day-of-week name. See report-formulas for the non-visible-sort-key pattern.

9. Reference — Math functions

   Numeric operations. All return num Data Type.

   Function

   What it does

   Expression

   Per-DB variants

   ABS

   Absolute (positive) value

   ABS(?)

   —

   ROUND

   Round to N decimal places

   ROUND(?, 2)

   —

   CEILING

   Round up to nearest integer

   CEILING(?)

   —

   FLOOR

   Round down to nearest integer

   FLOOR(?)

   —

   POWER

   Raise to a power

   POWER(?, n)

   —

   SQRT

   Square root

   SQRT(?)

   —

   MOD

   Remainder of division

   MOD(?, n)

   MSSQL: ? % n

   Math functions are the most portable category — six of the seven above are identical across all three databases. The integer-division-needs-casting pattern from the recipes section above is the one to remember.

10. Reference — Other (conditional & NULL handling)

    Conditional logic and NULL handling. These are the most-asked-about functions after aggregates — they show up in almost every advanced formula.

    Function

    What it does

    Default expression

    Per-DB variants

    CASE

    Multi-branch conditional (universal)

    CASE WHEN ? = 'X' THEN 'A' ELSE 'B' END

    —

    Inline IF

    Two-branch shorthand

    (use CASE)

    MSSQL: IIF(? > 0, 'matching_result', 'else_result') · MySQL: IF(? > 0, 'matching_result', 'else_result') · PG: use CASE

    COALESCE

    First non-NULL of N values

    COALESCE(?, [insert_fallback_field], 'default_value')

    —

    NULLIF

    NULL if value equals X, else the value

    NULLIF(?, 0)

    —

    NULL-to-value

    Two-argument NULL replacement

    COALESCE(?, 'default_value')

    MSSQL: ISNULL(?, 'default_value') · MySQL: IFNULL(?, 'default_value') · PG: use COALESCE

    Two practical notes:

    • Prefer COALESCE over ISNULL/IFNULL/NVL. COALESCE is part of the SQL standard and works on every database listed. The vendor-specific aliases (ISNULL on MSSQL, IFNULL on MySQL, NVL on Oracle) all do the same thing but reduce portability. If you ever migrate or write formulas across DBs, COALESCE saves rewrites.
    • NULLIF is the divide-by-zero hero. Almost every safe-divide pattern uses it. See the first recipe above.