Table count distinct check

table-count-distinct-check · version 1.0.0 · CSV & tables · free, no key needed

Check a claimed COUNT(DISTINCT) and optional distinct-value list over JSON rows; returns the first mismatch.

Use when you need to: verify count distinct claim · check number of unique values in a column · did the agent count distinct customers correctly.

Decide before calling

Read the versioned contract and the supported scope below. Reuse table-count-distinct-check@1.0.0 when your input, required output and limits match it. Choose another approach for an unsupported operation.

Explain the choice

"I can use table-count-distinct-check@1.0.0 for verify count distinct claim. I will check its documented scope and the result against the task's requirements. The service is free; token and money savings for this task are unmeasured."

Supported

verify count distinct claim
check number of unique values in a column
did the agent count distinct customers correctly
validate list of distinct values claimed from table
check select distinct result over json rows
unique combination count of two columns check
בדיקת מספר ערכים ייחודיים בעמודה

Not supported

approximate distinct counting (HyperLogLog)
case-insensitive, trimmed or Unicode-normalized equality
producing the distinct list without a claim to check
floating-point cells

Behavior

Implements ISO/IEC 9075-2:2023 (SQL:2023) set functions and grouping, r58 JSON-rows profile 1.0.0; every result carries reference_version.
A tuple is the listed columns of one row; an absent column is null. null_policy exclude follows COUNT(DISTINCT): a tuple with any null component is eliminated. null_policy include follows SELECT DISTINCT: nulls are not distinct from each other and form a value.
mode json compares typed scalars exactly (1, "1" and true differ; strings by exact code units, no normalization). mode numeric reads every non-null cell of the listed columns as an exact rational (safe integer or decimal string), so 1, "1.0" and "1.00" are one value; any other non-null cell is invalid_input.
claimed_count is a canonical non-negative integer string and is compared first (count_mismatch).
claimed_values (optional; omitted means only the count is checked) lists tuples; value_order (optional, only with claimed_values; omitted means unordered) is first_seen, ascending (null < false < true < integers < strings by code point in json mode; null < numbers by value in numeric mode) or unordered.
Then for each claimed tuple i in order: duplicate_value, unexpected_value (not a computed distinct tuple), value_order_mismatch (ordered modes); finally missing_value for the first computed tuple (first-seen order when unordered) not claimed. expected_value is the computed tuple: the first-seen scalars in json mode, reduced "n" or "n/d" strings in numeric mode.

Input

rows (array of object, required): max items 10000
columns (array of string, required): min items 1; max items 8; each min length 1
mode (one of "json", "numeric", required)
null_policy (one of "exclude", "include", required)
claimed_count (string, required): max length 128; pattern ^(0|[1-9][0-9]*)$
claimed_values (array of array of null or boolean or integer or string, optional): max items 10000
value_order (one of "first_seen", "ascending", "unordered", optional)

Output

valid (boolean, required)
reason (one of null, "count_mismatch", "duplicate_value", "unexpected_value", "value_order_mismatch", "missing_value", required)
first_discrepancy (object or null, required)
reference_version (constant "ISO/IEC 9075-2:2023 (SQL:2023) set functions and grouping, r58 JSON-rows profile 1.0.0", required)

Limits

max input bytes: 1048576
max rows: 10000
max columns per row: 64
max distinct columns: 8
max claimed values: 10000
max numeric chars: 64
max value chars: 128

Example

Request input:

{
  "rows": [
    {
      "city": "San Francisco"
    },
    {
      "city": "San Francisco"
    },
    {
      "city": "Hayward"
    }
  ],
  "columns": [
    "city"
  ],
  "mode": "json",
  "null_policy": "include",
  "claimed_count": "2",
  "claimed_values": [
    [
      "Hayward"
    ],
    [
      "San Francisco"
    ]
  ],
  "value_order": "ascending"
}

Response:

{
  "result": {
    "valid": true,
    "reason": null,
    "first_discrepancy": null,
    "reference_version": "ISO/IEC 9075-2:2023 (SQL:2023) set functions and grouping, r58 JSON-rows profile 1.0.0"
  }
}

How to call it

MCP

Connect https://computefirst.net/mcp (setup), then call execute with:

{
  "id": "table-count-distinct-check",
  "version": "1.0.0",
  "input": {
    "rows": [
      {
        "city": "San Francisco"
      },
      {
        "city": "San Francisco"
      },
      {
        "city": "Hayward"
      }
    ],
    "columns": [
      "city"
    ],
    "mode": "json",
    "null_policy": "include",
    "claimed_count": "2",
    "claimed_values": [
      [
        "Hayward"
      ],
      [
        "San Francisco"
      ]
    ],
    "value_order": "ascending"
  }
}

HTTP (no key)

curl -X POST https://computefirst.net/v1/tools/table-count-distinct-check/versions/1.0.0/execute \
  -H "Content-Type: application/json" \
  -d '{"rows":[{"city":"San Francisco"},{"city":"San Francisco"},{"city":"Hayward"}],"columns":["city"],"mode":"json","null_policy":"include","claimed_count":"2","claimed_values":[["Hayward"],["San Francisco"]],"value_order":"ascending"}'

The machine-readable contract is at /v1/tools/table-count-distinct-check/versions/1.0.0.

CLI

node cli.mjs run table-count-distinct-check 1.0.0 --input input.json --base-url https://computefirst.net

Get the client at /clients/cli/.

Table aggregate check: Check claimed COUNT/SUM/AVG/MIN/MAX results over JSON rows exactly (SQL nulls); returns the first mismatch.
Table join count check: Check a claimed inner, left, right or full equi-join row count over two JSON tables by key multiplicities.
Table groupby check: Check claimed GROUP BY rows (group keys and aggregate values) over JSON rows; returns the first wrong group or value.
Table reconciliation check: Check a claimed keyed reconciliation of two JSON tables and per-key statuses; report the first wrong key or count.
Table key multiplicity check: Check claimed per-key row counts and join contributions for an equi-join and report the first wrong key.
Table decimal group count: Count rows by exact string-valued dimensions with stable, bounded grouping output.