Aggregate / Group By
db_aggregateGroup rows by columns and compute count, sum, avg, min, max aggregations for analytical questions such as total sales per category or average order value per month.
Instructions
Groups rows by one or more columns and computes aggregate functions (count, sum, avg, min, max) on each group. This is the tool for analytical queries like "total sales by category", "average order value by month", or "count of users per country". Results are sorted by the aggregate by default.
When to use:
"How many products in each category?" (by=category, _count="*")
"Total revenue by region" (by=region, _sum="amount")
"Average order value by status" (by=status, _avg="total")
"Min and max prices per category" (by=category, _min="price", _max="price")
Parameter guidance:
by: comma-separated column names to group by (required). Example: "category, region"
where: optional JSON filter applied before grouping
orderBy: JSON object for sorting results. Use "_count", "_sum", "_avg", "_min", "_max" as the key. Example: {"_count": "desc"}
sum/avg/min/max: comma-separated numeric columns to aggregate
take: max groups to return (default 50)
Behavioral notes:
All aggregations run in a read-only transaction with a configurable timeout.
Results are returned as an array of group objects with the computed aggregates.
Groups with zero rows are excluded from the results.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| by | Yes | Comma-separated column names to group by | |
| avg | No | Comma-separated numeric columns to average | |
| max | No | Comma-separated columns to find maximum | |
| min | No | Comma-separated columns to find minimum | |
| sum | No | Comma-separated numeric columns to sum | |
| take | No | Max groups to return (default 50) | |
| table | Yes | Name of the table to query | |
| where | No | Optional JSON filter applied before grouping | |
| orderBy | No | JSON order object, e.g. '{"_count":"desc"}' | |
| database | No | Name of the database to query (from pgautopilot.json). Omit to use the current default database. |