# 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](/v1/tools/table-count-distinct-check/versions/1.0.0) 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:

```json
{
  "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:

```json
{
  "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](/docs#connect)), then call `execute` with:

```json
{
  "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)

```sh
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](/v1/tools/table-count-distinct-check/versions/1.0.0).

### CLI

```sh
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/](/clients/cli/).

## Related tools

- [Table aggregate check](/tools/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](/tools/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](/tools/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](/tools/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](/tools/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](/tools/table-decimal-group-count): Count rows by exact string-valued dimensions with stable, bounded grouping output.