Skip to main content
DocumentationLibraryReference
On this page

Kater SQL Functions Calculation context

SHARE_OF_TOTAL

Divides a measure on each row by its total at a broader grouping.

Family: Calculation context

Name the grouping with BY or ACROSS.

Syntax 

SHARE_OF_TOTAL(measure, BY(dimension, ...) | ACROSS(dimension, ...) [, SET(...)] [, IGNORE_FILTERS(...)])

Arguments

ArgumentDescription
measureThe measure to divide.
BY(...) or ACROSS(...)The grouping of the total. BY() divides by the grand total.
SET(...), IGNORE_FILTERS(...)Optional. More changes to the total’s context. They change only the total, not the row’s own value.

Returns

A fraction, such as 0.25 for 25 percent. It is null when the total is zero.

Usage notes

  • The total is worked out again, as TOTAL works it out.
  • Format it as a percent with a /* @format percent(1) */ comment after the column.
  • KATER__SHARE_OF_TOTAL is a window. It divides by the total of the rows in its partition, not by a measure worked out again.

Example

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

SELECT
  orders.channel AS "Channel",
  EVAL(orders.sum__quantity) AS "Units",
  SHARE_OF_TOTAL(orders.sum__quantity, BY()) AS "Share of Units" /* @format percent(1) */
FROM kater_dataset.orders AS orders
GROUP BY
  orders.channel
ORDER BY
  1
ChannelUnitsShare of Units
email2,38326.0%
paid2,29225.0%
store2,33025.5%
web2,14923.5%

See also