Skip to content

10. Aggregations and statistics

  • Understand what an API does when it returns “statistics”: COUNT, SUM, AVG, MIN, MAX computed by the database.
  • Read an aggregated response and know which tables and JOINs it comes from.
  • Use GET /stats and GET /stats/sales?group_by=…&from=…&to=… with curl, Python and TypeScript.
  • Draw a text chart from the aggregated numbers.

So far you have asked for rows: a book, a list of orders. Aggregations ask instead for summary numbers over many rows: how many books per category, revenue per month, an author’s average rating. In SQL you get them with aggregate functions and GROUP BY:

Function What it computes Example in the dataset
COUNT(*) number of rows in the group books per category
COUNT(DISTINCT x) distinct values distinct orders containing a book
SUM(x) sum revenue (quantity * unit_price)
AVG(x) average average price
MIN(x) / MAX(x) extremes cheapest / most expensive book

GROUP BY column splits the rows into groups (one per value of the column) and every function works inside the group. HAVING filters the groups after aggregation (while WHERE filters the rows before).

A “well-made” API does not make you download 300 rows of order_items to add them up on the client: it exposes an endpoint that does the calculation in the database and returns only the result. It is faster and, above all, the result is consistent (a single source of truth).

The overall picture of the catalog:

terminal
curl -s https://corso-api-json.giuseppebosi.com/api/v1/stats | python3 -m json.tool
curl -s
a silent GET request (no progress bar)
/api/v1/stats
an aggregation endpoint: no parameters, it returns a fixed summary
|
the shell pipe: the output of curl becomes the input of the next command
python3 -m json.tool
reads JSON from standard input and prints it indented (handy for reading long responses)

Response (shortened):

{
"totals": { "books": 80, "authors": 20, "customers": 40, "orders": 120, "reviews": 243 },
"prices": { "min": 7.13, "avg": 19.9, "max": 34.49 },
"books_by_category": [
{ "slug": "saggistica", "name": "Saggistica", "books": 14, "avg_price": 17.04 },
{ "slug": "ragazzi", "name": "Ragazzi", "books": 12, "avg_price": 16.72 },
{ "slug": "cucina", "name": "Cucina", "books": 9, "avg_price": 18.39 },
…
],
"top_authors": [
{ "id": 18, "name": "Matteo Rinaldi", "books": 8, "avg_rating": 4.22 },
{ "id": 1, "name": "Elena Marchetti", "books": 6, "avg_rating": 3.12 },
…
],
"orders_by_status": [
{ "status": "cancelled", "orders": 18, "revenue": 1304.45 },
{ "status": "delivered", "orders": 48, "revenue": 3820.12 },
…
]
}

Read it with the functions in mind: totals are five COUNT(*)s; prices is MIN/AVG/MAX(price) over books; books_by_category is a GROUP BY categories.id with COUNT(b.id) and AVG(b.price); orders_by_status a GROUP BY status with COUNT and SUM(total).

Now sales per month, limited to a period:

terminal
curl -s 'https://corso-api-json.giuseppebosi.com/api/v1/stats/sales?group_by=month&from=2025-01-01&to=2025-04-01'
'…'
single quotes around the URL: in many shells & means "run in the background"; inside quotes it is a normal character
?group_by=month
the query string starts with ?; group_by picks the GROUP BY column: month, category, author or city
&from=2025-01-01
& separates the parameters; from is the start date (included), ISO 8601 format
&to=2025-04-01
end date (excluded): January, February and March
{
"group_by": "month",
"items": [
{ "month": "2025-01", "orders": 5, "books_sold": 14, "revenue": 367.53 },
{ "month": "2025-02", "orders": 4, "books_sold": 12, "revenue": 221.34 },
{ "month": "2025-03", "orders": 6, "books_sold": 25, "revenue": 487.03 }
]
}

Three aggregated columns per group: orders is COUNT(DISTINCT o.id) (an order with 3 lines counts once), books_sold is SUM(oi.quantity), revenue is SUM(oi.quantity * oi.unit_price). cancelled orders are excluded by a WHERE before grouping.

Sales per city, turned into a text bar chart. The heavy lifting (sums and groupings) is done by the server; the client only sorts and draws.

# pip install httpx → python3 vendite.py
import httpx
BASE = "https://corso-api-json.giuseppebosi.com/api/v1"
r = httpx.get(f"{BASE}/stats/sales", params={"group_by": "city"}, timeout=10)
r.raise_for_status() # solleva un'eccezione se lo status non è 2xx
righe = r.json()["items"]
massimo = max(x["revenue"] for x in righe)
for x in sorted(righe, key=lambda x: -x["revenue"]):
barra = "█" * round(30 * x["revenue"] / massimo)
print(f"{x['city']:<8} {barra:<30} {x['revenue']:>8.2f} € ({x['orders']} ordini)")

What you should see:

Torino ██████████████████████████████ 1715.02 € (20 ordini)
Padova ████████████████████████████ 1609.57 € (20 ordini)
Firenze █████████████████████████ 1423.28 € (16 ordini)
Napoli ███████████████████ 1059.56 € (12 ordini)
Verona █████████████ 749.61 € (9 ordini)
Genova ████████████ 658.60 € (10 ordini)
Bari ███████████ 614.68 € (8 ordini)
Bologna █████████ 496.76 € (6 ordini)
Roma ██ 93.14 € (1 ordini)

Output (identical in both languages):

Torino ██████████████████████████████ 1715.02 € (20 ordini)
Padova ████████████████████████████ 1609.57 € (20 ordini)
Firenze █████████████████████████ 1423.28 € (16 ordini)
Napoli ███████████████████ 1059.56 € (12 ordini)
Verona █████████████ 749.61 € (9 ordini)
Genova ████████████ 658.60 € (10 ordini)
Bari ███████████ 614.68 € (8 ordini)
Bologna █████████ 496.76 € (6 ordini)
Roma ██ 93.14 € (1 ordini)
GET/api/v1/stats— catalog summary
GET/api/v1/stats/sales?group_by=category— sales per category
GET/api/v1/stats/sales?group_by=author&from=2025-06-01— sales per author since June 2025

Try it online, nothing to install:

Open the notebook in Google Colab Open the TypeScript project in StackBlitz

The GET request straight in your browser: https://corso-api-json.giuseppebosi.com/api/v1/stats

Python on Google Colab (a free Google account is needed to run the cells); TypeScript on StackBlitz (it opens and starts by itself in the browser). If the practice API is switched off, turn it on from /en/api-attiva/.

  1. Which category has the highest revenue? Is it the same one with the most books sold? (use group_by=category).
  2. Write a script that calls /stats/sales?group_by=month and prints the best and the worst month.
  3. Why does orders in /stats/sales use COUNT(DISTINCT o.id) and not COUNT(*)?
Solution
  1. With group_by=category: saggistica (non-fiction) has the highest revenue (€1,489.55) and is also first for copies sold (82), followed by narrativa (fiction: €1,460.82 and 64 copies). Here the two rankings coincide, but it is not a given: further down, cucina (cooking) sells 39 copies with lower revenue (€742.25) than fantascienza (sci-fi), which sells 28 (€834.10). It depends on the average price.
  2. Once you have items, max(items, key=lambda x: x["revenue"]) and min(...) in Python; in TS sort with sort and take the first and the last.
  3. Because the query joins orders with order_items: an order with three lines appears three times in the JOIN. COUNT(*) would count the rows, COUNT(DISTINCT o.id) the orders.
  • Aggregating on the client by downloading every row: slow, and with pagination you risk adding up only the first page.
  • Confusing WHERE and HAVING: from/to filter the rows (WHERE), “only categories with more than 5 books” filters the groups (HAVING).
  • Forgetting the statuses: revenue that includes cancelled orders is wrong. Always ask the API (or the documentation) what it excludes.
  • Averages of averages: the average of the per-category average prices is not the average price of the catalog.

Aggregations are numbers computed by the database over groups of rows; the API exposes them as dedicated endpoints (/stats, /stats/sales) with parameters to choose the grouping and the period. In the next chapter you switch sides: you will build the query yourself, in JSON, with POST /query.