How do I calculate an average per group?
Recipe verified on
Ran on the public demo (read-only)
Short answer
Ask for the average of the value column grouped by the category you care about. Your AI sends avg with a group-by and gets one exact average per group, here the average order value per sales channel.
Steps
- Connect your assistant to the public demo. It's a read-only instance with 10 million synthetic orders,
so you can try this before you have your own. In Claude Code:
claude mcp add --transport http mcpdata-demo https://demo.mcpdata.org/mcp --header "Authorization: Bearer cdb_live_PkVoldus8qcE-hqffjajNZzQwiA7rTHMoR4RLSAxsCE"
The key is public on purpose: it can only read the demo's sample data. For Claude Desktop, Cursor and other apps, see Connect your AI.
- Ask your question. Give your AI this prompt:
Using mcpdata, what is the average order value per channel in Demo.Orders? Include the number of orders.
What your AI does: the tool call it makes for this prompt, and what came back
query(table_name="Demo.Orders", group_by=["channel"], agg=[{"func": "avg", "column": "revenue", "alias": "avg_order_value"}, {"func": "count", "column": "order_id", "alias": "orders"}], sort=[{"column": "avg_order_value", "desc": true}]) → 3 rows
| channel | avg_order_value | orders |
|---|---|---|
| Online | 536.50 | 5,501,941 |
| Store | 535.80 | 3,301,229 |
| Partner | 535.60 | 1,201,830 |
Synthetic data: the demo's orders are generated, not real customers.
The assistant needs mcpdata connected (step 1) to run the query.
Why this works
An average of averages is a classic mistake when an AI combines partial results. Computed in the database over all rows, each group's average is the true one. Asking for the count alongside shows how much each average rests on.