Table groupby check

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

Check claimed GROUP BY rows (group keys and aggregate values) over JSON rows; returns the first wrong group or value.

Use when you need to: verify group by result rows · check per group totals claimed by an agent · validate pivot summary counts per category.

Decide before calling

Read the versioned contract and the supported scope below. Reuse table-groupby-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-groupby-check@1.0.0 for verify group by result rows. 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 group by result rows
check per group totals claimed by an agent
validate pivot summary counts per category
check grouped sum and count per key in json table
are these group by aggregates correct
verify subtotals by customer and month
בדיקת סיכום לפי קבוצות בטבלה

Not supported

producing the grouped table (this only checks a claimed one)
HAVING filters, ROLLUP, CUBE or GROUPING SETS
grouping by computed expressions or numeric equality of "1" and 1
floating-point cells or tolerance comparisons

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.
Groups are formed by the exact typed tuple of the group_by cells; an absent column is null and all nulls fall in one group (SQL grouping treats nulls as not distinct). 1, "1" and true are different keys; strings are compared by exact code units with no normalization.
Set functions per group follow table-aggregate-check: count_star, count, and null-ignoring sum, avg, min, max over safe integers and decimal strings as exact rationals; a group whose cells are all null has sum/avg/min/max null. An empty table has no groups.
row_order is required: first_seen (order of first appearance in rows), key_ascending (null < false < true < integers by value < strings by Unicode code point, compared column by column) or unordered.
Comparison order: for each claimed row i in order: duplicate_group (key equals an earlier claimed key), unexpected_group (no such group), group_order_mismatch (ordered modes: the computed group at position i differs), then aggregate_mismatch for each value j in order; finally missing_group for the first computed group (in first-seen order for unordered) that was not claimed.
Claimed values are null or integer, p/q or decimal strings compared by exact value; computed values are reduced "n" or "n/d". All rows, claims and numeric columns are validated before any comparison.

Input

rows (array of object, required): max items 10000
group_by (array of string, required): min items 1; max items 8; each min length 1
aggregates (array of object, required): max items 16
row_order (one of "first_seen", "key_ascending", "unordered", required)
claimed_rows (array of object, required): max items 10000

Output

valid (boolean, required)
reason (one of null, "duplicate_group", "unexpected_group", "group_order_mismatch", "aggregate_mismatch", "missing_group", 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 group columns: 8
max aggregates: 16
max claimed rows: 10000
max numeric chars: 64
max value chars: 128

Example

Request input:

{
  "rows": [
    {
      "city": "San Francisco",
      "temp_lo": 46
    },
    {
      "city": "San Francisco",
      "temp_lo": 43
    },
    {
      "city": "Hayward",
      "temp_lo": 37
    }
  ],
  "group_by": [
    "city"
  ],
  "aggregates": [
    {
      "function": "count_star"
    },
    {
      "function": "max",
      "column": "temp_lo"
    }
  ],
  "row_order": "key_ascending",
  "claimed_rows": [
    {
      "key": [
        "Hayward"
      ],
      "values": [
        "1",
        "37"
      ]
    },
    {
      "key": [
        "San Francisco"
      ],
      "values": [
        "2",
        "46"
      ]
    }
  ]
}

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-groupby-check",
  "version": "1.0.0",
  "input": {
    "rows": [
      {
        "city": "San Francisco",
        "temp_lo": 46
      },
      {
        "city": "San Francisco",
        "temp_lo": 43
      },
      {
        "city": "Hayward",
        "temp_lo": 37
      }
    ],
    "group_by": [
      "city"
    ],
    "aggregates": [
      {
        "function": "count_star"
      },
      {
        "function": "max",
        "column": "temp_lo"
      }
    ],
    "row_order": "key_ascending",
    "claimed_rows": [
      {
        "key": [
          "Hayward"
        ],
        "values": [
          "1",
          "37"
        ]
      },
      {
        "key": [
          "San Francisco"
        ],
        "values": [
          "2",
          "46"
        ]
      }
    ]
  }
}

HTTP (no key)

curl -X POST https://computefirst.net/v1/tools/table-groupby-check/versions/1.0.0/execute \
  -H "Content-Type: application/json" \
  -d '{"rows":[{"city":"San Francisco","temp_lo":46},{"city":"San Francisco","temp_lo":43},{"city":"Hayward","temp_lo":37}],"group_by":["city"],"aggregates":[{"function":"count_star"},{"function":"max","column":"temp_lo"}],"row_order":"key_ascending","claimed_rows":[{"key":["Hayward"],"values":["1","37"]},{"key":["San Francisco"],"values":["2","46"]}]}'

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

CLI

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

Get the client at /clients/cli/.

Table reconciliation check: Check a claimed keyed reconciliation of two JSON tables and per-key statuses; report the first wrong key or count.
Table count distinct check: Check a claimed COUNT(DISTINCT) and optional distinct-value list over JSON rows; returns the first mismatch.
Table key multiplicity check: Check claimed per-key row counts and join contributions for an equi-join and report the first wrong key.
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 decimal group sum: Group table rows by string keys and compute exact decimal sum results with deterministic ordering.