examples · sql · 7 / 13

7. GROUP BY + aggregates

what this does

Bucket rows by status and compute multiple aggregates per bucket in a single pass - row count, sum, average, min, and max of amount_cents.

when to use it

Reporting dashboards: "orders per status", "revenue per customer", "rows per day".
You can mix any number of COUNT / SUM / AVG / MIN / MAX in one query - they all share the same scan.
GROUP BY can list multiple columns: GROUP BY status, region.

the request

POST /v1/tenants/:t/sql

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT status, COUNT(*) AS n, SUM(amount_cents) AS total, AVG(amount_cents) AS avg_amt, MIN(amount_cents) AS min_amt, MAX(amount_cents) AS max_amt FROM shop.orders GROUP BY status"
  }'

result = db.sql("""
    SELECT status,
           COUNT(*) AS n,
           SUM(amount_cents) AS total,
           AVG(amount_cents) AS avg_amt,
           MIN(amount_cents) AS min_amt,
           MAX(amount_cents) AS max_amt
      FROM shop.orders
     GROUP BY status
""")

const result = await db.sql(`
  SELECT status,
         COUNT(*) AS n,
         SUM(amount_cents) AS total,
         AVG(amount_cents) AS avg_amt,
         MIN(amount_cents) AS min_amt,
         MAX(amount_cents) AS max_amt
    FROM shop.orders
   GROUP BY status
`);

result, _ := db.SQL(ctx, `
    SELECT status,
           COUNT(*) AS n,
           SUM(amount_cents) AS total,
           AVG(amount_cents) AS avg_amt,
           MIN(amount_cents) AS min_amt,
           MAX(amount_cents) AS max_amt
      FROM shop.orders
     GROUP BY status
`)

what you get back

{
  "kind": "select",
  "rows": [
    { "status": "paid",    "n": 2, "total":  6240, "avg_amt": 3120.0,  "min_amt": 1250, "max_amt":  4990 },
    { "status": "pending", "n": 1, "total": 12900, "avg_amt": 12900.0, "min_amt": 12900, "max_amt": 12900 }
  ]
}

common mistakes

HAVING not supported yet. To filter on aggregates (e.g. "groups with COUNT > 3"), filter the result client-side. On the SQL roadmap.
Selecting a non-grouped column. Every column in SELECT must either be in GROUP BY or wrapped in an aggregate. Standard SQL rule.
COUNT(DISTINCT col). Not supported yet - count distinct values client-side, or use an uncorrelated sub-SELECT.