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

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

```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-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)

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

### CLI

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

Get the client at [/clients/cli/](/clients/cli/).

## Related tools

- [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 count distinct check](/tools/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](/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 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 decimal group sum](/tools/table-decimal-group-sum): Group table rows by string keys and compute exact decimal sum results with deterministic ordering.