Group By

What you'll learn

Group records by a field value and count or aggregate within each group.

Group by query shape

{
  "type":       "group_by",
  "group_field": "plan",
  "agg_fn":     "count | sum | avg | min | max",
  "agg_field":  "amount",
  "limit":      50,
  "filter":     { ... }
}

Count users per plan

curl -s -X POST http://localhost:7475/v1/agents/users/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d '{
    "type":        "group_by",
    "group_field": "plan",
    "agg_fn":      "count"
  }' | python3 -m json.tool

Response: `json { "groups": [ {"group": "free", "count": 142, "value": 142}, {"group": "starter", "count": 38, "value": 38}, {"group": "pro", "count": 21, "value": 21}, {"group": "enterprise", "count": 4, "value": 4} ] } `

Sum revenue per product category

curl -s -X POST http://localhost:7475/v1/agents/orders/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -d '{
    "type":        "group_by",
    "group_field": "category",
    "agg_fn":      "sum",
    "agg_field":   "amount"
  }' | python3 -m json.tool

Challenge

Write 30 event records with event_type values of page_view, click, signup. Group by event_type and count each. Then group orders by currency and sum amount per currency.

---