Documentation

    Summarize

    Calculate totals, counts and other summaries for your whole table or for groups such as product categories. Choose one action for each column you want in the result.

    Summarize amounts by category

    Manual Input has four rows with category and amount. Summarize groups by category, sums amount and counts the rows in each group.

    The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.

    Result
    categorysum_amountcount
    Tea302
    Coffee302
    1. Change the Sum action to Average. Tea produces 15 and Coffee produces 30; the missing Coffee amount is ignored.
    2. Change that action's Output to average_amount to give the column a clearer name.
    3. Remove the Group by action. The output becomes one row for the whole table, with an average of 20 and a count of 4.

    When to use

    Use Summarize when you want totals, counts or averages for the whole table or for each group, with one row per group. To add a running total and keep every row, use Rolling Calc.

    Read the output

    At first, the output has two rows with category, sum_amount and count.

    Tea has a sum of 30 from 10 and 20, with a count of 2. Coffee also has a sum of 30 and a count of 2. Its missing amount isn't added to Sum, but its row still counts toward Count.

    Groups appear in the order they first show up in the input. Your actions set the output columns and their order.

    To report the dimensions of a whole table, use Count Dataset.

    To turn group values into separate columns, use Cross Tab.

    Inputs and outputs

    Inputs

    1 port
    InRequired

    Accepts: Tables, Geospatial data.

    Accepts one connection on this port.

    Outputs

    1 port
    Out

    Configure the block

    Build the result one action at a time, starting with any grouping columns.

    Add

    Add an action for each output column. Add Group by actions first, then calculations such as Sum and Count.

    Action

    Group by creates one result per distinct combination of group values. Other actions calculate a summary for each group, or for the whole table when no grouping is selected.

    Column

    Choose the source for the action. Count needs no column because it counts rows. Available columns depend on the action.

    Output

    Name the result column. Defaults use the source name for Group by, count for Count and an action prefix for calculations, such as sum_amount.

    Ignore Zeros

    For supported numeric summaries, exclude zero values from the calculation. This doesn't remove rows from other actions such as Count.

    Percentile (0–1)

    For Percentile, choose a position from 0 to 1. For example, 0.5 gives the median.

    Weight

    For Weighted avg, choose the numeric weight column. Rows missing either the value or its weight are excluded from that calculation.

    Separator

    For Concatenate, choose the text between joined values. Default: ,.

    Summarize FAQs

    What is the difference between Count and Count non-null?
    Count includes every row. Count non-null counts only rows with a value in the selected column. In this example, Coffee has Count 2 but Count non-null for amount is 1.
    How are missing values handled?
    Sum and Average ignore NULL values and return NULL when a group has no other values. Missing group values form their own group. Count blank counts NULL and empty strings, but not strings with only spaces.
    Is the original table still in the output?
    No. The output has only the columns your actions define, with one row per group, or one row in total when there's no grouping.

    Prepare your data. Build it in Strata.

    Strata opens in your browser, with no account.