Sql
Summarize rows by status
The language tabs use earlier client interfaces. Check SDK versions and connection setup before running them.
Group orders by status and calculate counts and amounts. An aggregate turns several rows into a summary value, such as SUM or COUNT.
Before you run it: Every selected field must be grouped or calculated by an aggregate. HAVING filters the finished groups.
- Start with individual ordersRead each row's status and amount.
- Group equal statusesCollect paid and pending orders into their groups.
- Calculate each groupCount orders and compute the requested sum or average.
- Filter group results if neededHAVING applies after the group calculation.
Follow the example
- Load the order sample.
- Group by status.
- Run the count, total and average query.
- Read one summary row per status.
Run the request
First register and load the orders sample: shop.orders has id, customer_id, amount_cents and status. Use its three sample rows for the results shown here.
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"
}'Check the result
The result contains one row per group rather than one row per order.
{
"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 }
]
}Important limits and checks
- Selecting a non-grouped column. Every column in SELECT must either be in GROUP BY or wrapped in an aggregate. Standard SQL rule.
- Filtering on aggregates. Use
HAVING- it executes server-side (e.g.GROUP BY status HAVING COUNT(*) > 3). It runs after grouping, so it can reference aggregates thatWHEREcan't.