KATER__SHARE_OF_TOTAL

Family: Windows

Syntax

1
KATER__SHARE_OF_TOTAL(field) OVER ([PARTITION BY ...])

Arguments

Returns

A fraction, such as 0.25 for 25%. The field menu writes @format percent(1) beside it, so it shows as a percentage. NULL where the total is 0.

Usage notes

• Write OVER (PARTITION BY ...) to work within each group, or OVER () for every row. It orders the rows itself, so its OVER takes no ORDER BY.
• It adds up the rows of the result. A measure that cannot be added up across a dimension, such as Revenue across Currency, needs that dimension in PARTITION BY.
• A negative value gives a negative share.

Example

This Sandbox query reads the sample shop’s orders. The table shows the rows that Kater gave.

1
SELECT
2
  orders.channel AS "Channel",
3
  EVAL(orders.sum__quantity) AS "Units",
4
  CUSTOM_CALCULATION(
5
    'share_of_total__sum__quantity',
6
    KATER__SHARE_OF_TOTAL(orders.sum__quantity) OVER ()
7
  ) AS "Share of Units" /* @format percent(1) */
8
FROM kater_dataset.orders AS orders
9
GROUP BY
10
  orders.channel
11
ORDER BY
12
  1

Compiles to

The call compiles to the same SQL on DuckDB and on Snowflake.

1
CAST(orders.sum__quantity AS DOUBLE) / NULLIF(SUM(orders.sum__quantity) OVER (), 0)

See also

• Running totals, ranks and windows
• Other functions in Windows: KATER__WINDOW_AGGREGATION, KATER__LAG, KATER__LEAD, KATER__FIRST_VALUE, KATER__LAST_VALUE, KATER__DELTA, KATER__PERCENT_DELTA, KATER__RANK, KATER__ROW_NUMBER