Shape / table · All operations

Count values

Frequency table, value counts, count by.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
a new grain: each row stands for a group of input rows
Level
one clause of SQL

The DuckDB step

A frequency table with share and bar

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT product, count(*) AS n, round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct,
       bar(count(*), 0, max(count(*)) OVER (), 20) AS histogram
FROM orders GROUP BY product ORDER BY n DESC;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT sum(n) = 10 AND sum(pct) = 100 AND max(n) FILTER (WHERE product = 'widget') = 4 AND max(pct) FILTER (WHERE product = 'widget') = 40 FROM result)) THEN 'as expected' ELSE error('the counts do not sum to 10 rows and 100 percent with widget at 4') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the counts do not sum to 10 rows and 100 percent with widget at 4”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (1)
  • Frequency Tableread 2026-09-27

    Produce counts and percentages for each value of the selected fields

Apache NiFi (1)
  • CalculateRecordStatsread 2026-09-27

    Count records in a record set, and count per category for user-defined category paths

dbt-utils (1)
  • get_column_valuesread 2026-09-27

    Run a query at compile time and return the distinct values of a column, ordered by frequency by default

Duckle (1)
  • Histogramread 2026-09-25

    value frequencies for a column

KNIME Analytics Platform (1)
  • Value Counterread 2026-09-27

    Count the occurrences of each distinct value in one column

OpenRefine (9)
  • Best candidate's name matchread 2026-09-27

    Facet cells by whether the best candidate's name matched the cell

  • Best candidate's type matchread 2026-09-27

    Facet cells by whether the best candidate matched the type reconciled against

  • Best candidate's typesread 2026-09-27

    Facet cells by the types the service returned for the best candidate

  • By judgmentread 2026-09-27

    Facet cells by reconciliation judgment: matched, new or none

  • Custom text facetread 2026-09-27

    Text facet on the result of an expression, leaving the data unchanged

  • Judgment action timestampread 2026-09-27

    Facet cells by the time at which their judgment was made

  • Judgment action typeread 2026-09-27

    Facet cells by how they were judged: single match, mass match or no action

  • Text facetread 2026-09-27

    Count the distinct values of a column and filter rows by picked values; entries can be edited in bulk

  • Word facetread 2026-09-27

    Split cell text on spaces and count each word as a facet choice

Polars (2)
  • Expr.unique_countsread 2026-09-27

    Count of each distinct value, in order of first appearance

  • Expr.value_countsread 2026-09-27

    Count the occurrences of each distinct value, as structs of value and count

VisiData (3)
  • freq-colread 2026-09-23

    Open Frequency Table grouped on current column: count, percent, histogram, aggregates

  • freq-keysread 2026-09-23

    Open Frequency Table grouped by all key columns

  • freq-rowread 2026-09-23

    From Columns Sheet, open Frequency Table grouped on the column in current row

Background

A frequency table arranges the values a variable takes in a sample, giving the count of occurrences of each value or interval.[1] The relative frequency of a value is its count divided by the sample size, the ratio of absolute frequency to the total.[1] In DuckDB, grouping a table by a column and taking count(*) counts the entries that belong to each different value of that column.[2]

When to use it

An OpenRefine text facet matches up the whole contents of a column's cells and does no guessing about typos or near-matches.[3] In OpenRefine, editing an entry in a text facet mass-edits every identical cell in the column, which makes the facet a way to fix typos and whitespace.[3] Counting the values of a column is one of the quickest ways to find variant spellings, stray whitespace and placeholder values before anything is joined or summed. (Meridian's view)

In DuckDB, count(*) returns the number of rows, while count(arg) returns the number of rows where arg is not NULL.[4] In SQLite, NULL values are considered equal for the purposes of grouping rows, so the NULLs of a grouped column share one group.[5] The pandas value_counts method excludes NA values by default and shows them only when its dropna parameter is set to False.[6]

In Polars, value_counts returns its counts in a non-deterministic order unless its sort option is set, which sorts by count in descending order.[7] The pandas value_counts method returns its counts in descending order by default, so the first element is the most frequently occurring one.[6] Say which order a frequency table is read in, by count or by value, and break ties between equal counts explicitly, so the same data always gives the same table. (Meridian's view)

A different tabulation scheme aggregates the values of a variable into bins, each encompassing a range of values, rather than counting each value.[1] The pandas value_counts method can group numeric values into half-open bins instead of counting each unique value, through its bins parameter.[6]

The method

In statistics, the frequency or absolute frequency of an event is the number of times the observation has occurred or been recorded in an experiment or study.[1] The relative frequency of an event is its absolute frequency normalised by the total number of events, and the relative frequencies of all events can be plotted to produce a frequency distribution.[1] The cumulative frequency is the total of the absolute frequencies of all events at or below a certain point in an ordered list of events.[1]

In DuckDB, a GROUP BY clause combines all rows that have matching data in the grouping columns, and the other columns can be combined using an aggregate function such as count.[2] In DuckDB, the histogram aggregate returns a map of key-value pairs representing buckets and counts.[4] In DuckDB, the mode aggregate returns the most frequent value.[4]

Compared with the alternatives

A contingency table, also known as a cross tabulation or crosstab, displays the multivariate frequency distribution of its variables in a matrix format.[8] Bivariate joint frequency distributions are often presented as two-way contingency tables, whose total row and total column report the marginal frequencies.[1] The term contingency table was first used by Karl Pearson, in a memoir published in 1904.[8] A pivot table is a way to create contingency tables using spreadsheet software.[8]

A histogram represents tabulated frequencies as adjacent rectangles erected over discrete intervals, with an area proportional to the frequency of the observations in each interval.[1] The term histogram was first introduced by Karl Pearson in lectures delivered in 1892 at University College London.[9]

In SQLite, SELECT DISTINCT removes duplicate rows from the result, and two NULL values are considered equal when it does.[5]

Sources

  1. [1]Frequency (statistics) - Wikipediasecondary source
  2. [2]GROUP BY Clause – DuckDBdocumentation
  3. [3]Exploring facets | OpenRefinedocumentation
  4. [4]Aggregate Functions – DuckDBdocumentation
  5. [5]SELECTdocumentation
  6. [6]pandas.Series.value_counts — pandas 3.1.0 documentationdocumentation
  7. [7]polars.Expr.value_counts — Polars documentationdocumentation
  8. [8]Contingency table - Wikipediasecondary source
  9. [9]Histogram - Wikipediasecondary source