Asqueel

Course / Orientation / Lesson 05

Lesson 05 · Orientation

Aggregate related records

Summarize related records with invoice, customer and product totals, counts and averages; filter products by average selling price.

2:37 · video8 scenesPrerequisites: the in-memory Python session from lesson 4

Asqueel 0.6.0 · Python console demonstration · API compatibility

Course contents
Orientation
  1. 01 Welcome: your first table
  2. 02 Customers, invoices and relationship paths
  3. 03 Filter and sort through relationships
  4. 04 Aliases and formula columns
  5. 05 Aggregate related records
  6. 06 Group by and HAVING
  7. 07 Fetch and export
  8. 08 Optional: find the best buyer
  9. 09 Writes and transactions
  10. 10 Python triggers
The model
  1. 11 virtualRelation
  2. 12 Derived columns: aliasColumn, formulaColumn
  3. 13 Advanced formulas
  4. 14 subQueryColumn and bagItemColumn
  5. 15 Layered recipes
  6. 16 Extending the grammar
  7. 17 The model hosts external metadata
Reading data
  1. 18 query(...): syntax and clauses
  2. 19 Parameters and macros
  3. 20 Fetch terminals
  4. 21 The environment
  5. 22 Row policies
  6. 23 Locking on read: for_update
Writing data
  1. 24 insert, update, delete
  2. 25 The order of the write cycle
  3. 26 Field triggers
  4. 27 Relation actions: Python and SQL
  5. 28 Writes without triggers: sql_*, table.lock
  6. 29 sys_fields, hierarchical, auto_counter
  7. 30 recordToUpdate and batchUpdate
Transactions & operations
  1. 31 Transactions and connections
  2. 32 Deferred callbacks
  3. 33 Direct SQL
  4. 34 Import and migrations
  5. 35 The command line
  6. 36 Integration in a host application
  7. 37 The complete example
Other Python ORMs
  1. 38 asqueel, SQLAlchemy, Django and Peewee
WATCH THE LESSON2:37 · English
Download MP4 ↓English captions ↓

Follow the chapters, then try the examples yourself. The complete transcript and code are below.

Try it yourself

Run this lesson in Google Colab, with the starting data included. A Google account is required to execute code. You can also download the notebook for local Jupyter.

Try it yourself ↗Download notebook ↓

Download this example ↓ · Includes four CSV files and console commands. Put the CSV files in your working folder. If Python was closed, use start-here.py to recreate lessons one through four.

Download individual CSV files: customers.csv · invoice_rows.csv · invoices.csv · products.csv

Scene 01

What we'll learn in this lesson

Welcome back! Here's what we'll learn in this lesson. Summarize related records. We will build invoice, customer and product totals, ask for counts and averages, then filter products by average selling price. We reuse the row formula from lesson four.

Scene 02

Give every invoice its total

Let's give each invoice a calculated total. Declare the calculation once in the model. Select its rows and add their calculated amounts. THIS id is the current invoice's key. Let's read those totals as ordinary fields. The query only asks for number and total. The calculation stays in the model.

Example & screen notes
>>> 
>>> ######## Give each invoice a calculated total ########
>>> 
>>> _ = invoice.formulaColumn(
...     "total", dtype="N",
...     select=dict(
...         table="shop.invoice_row",
...         columns="ROUND(SUM($calculated_total), 2)",
...         where="$invoice_id = #THIS.id",
...     ),
... )
>>> db.reloadModel(model)
>>> invoices = db.table("shop.invoice")

>>> 
>>> ######## Read those totals as ordinary fields ########
>>> 
>>> invoices.query(
...     columns="$number, $total", order_by="$id", limit=3,
... ).fetch()
[[number=INV-001,total=34.9], [number=INV-002,total=48.7], [number=INV-003,total=25.3]]
>>> db.commit()

Scene 03

Give every customer their total

Let's total the invoices of each customer. Reuse the invoice total we just declared. THIS id now belongs to the customer. Let's read each customer's sales total. Ada has two invoices. Together they make one hundred fifty-four ten.

Example & screen notes
>>> 
>>> ######## Total the invoices of each customer ########
>>> 
>>> _ = customer.formulaColumn(
...     "total", dtype="N",
...     select=dict(
...         table="shop.invoice",
...         columns="ROUND(SUM($total), 2)",
...         where="$customer_id = #THIS.id",
...     ),
... )
>>> db.reloadModel(model)
>>> customers = db.table("shop.customer")

>>> 
>>> ######## Read each customer's sales total ########
>>> 
>>> customers.query(
...     columns="$name, $total", order_by="$id", limit=3,
... ).fetch()
[[name=Ada,total=154.1], [name=Grace,total=189.7], [name=Linus,total=57.2]]
>>> db.commit()

Scene 04

Give every product its sales total

Let's total the sales of each product. Add the amounts of invoice rows carrying its code. This is sales revenue, not the quantity in stock. Let's read the product totals. Again, the query uses a named field. Products with no sales have a null total.

Example & screen notes
>>> 
>>> ######## Total the sales of each product ########
>>> 
>>> _ = product.formulaColumn(
...     "total", dtype="N",
...     select=dict(
...         table="shop.invoice_row",
...         columns="ROUND(SUM($calculated_total), 2)",
...         where="$product_code = #THIS.code",
...     ),
... )
>>> db.reloadModel(model)
>>> products = db.table("shop.product")
>>> rows = db.table("shop.invoice_row")

>>> 
>>> ######## Read the product totals ########
>>> 
>>> products.query(
...     columns="$code, $name, $total",
...     where="$code IN :codes", codes=["TEA", "COF", "MUG"],
...     order_by="$code",
... ).fetch()
[[code=COF,name=Coffee,total=34.5], [code=MUG,name=Mug,total=26.25], [code=TEA,name=Green tea,total=17.5]]
>>> db.commit()

Scene 05

Sum, count and average the related invoices

Now let's ask for several aggregates at once. A customer reaches its invoices through the inverse name declared in lesson two. Each customer still produces one result row. Let's summarize each customer's invoices. Aggr adds their totals, counts the invoices, and averages their amounts. Asqueel names the returned columns automatically. Let's count the rows of each invoice too. The same aggregation syntax follows rows. Count returns the number of invoice rows, not the sum of their quantities.

Example & screen notes
customer → @invoices
invoice  → @rows
product  → @rows

sum($total) → sales amount
count()    → number of invoices
avg($total) → average invoice amount

>>> 
>>> ######## Summarize each customer's invoices ########
>>> 
>>> customers = db.table("shop.customer")
>>> customers.query(
...     columns="$name, @invoices.aggr("
...             "sum($total), count(), avg($total))",
...     order_by="$id", limit=3,
... ).fetch()
[[name=Ada,_invoices_total_sum=154.1,_invoices_count=2,_invoices_total_avg=77.05], [name=Grace,_invoices_total_sum=189.7,_invoices_count=2,_invoices_total_avg=94.85], [name=Linus,_invoices_total_sum=57.2,_invoices_count=2,_invoices_total_avg=28.6]]
>>> db.commit()

>>> 
>>> ######## Count the rows of each invoice too ########
>>> 
>>> invoices = db.table("shop.invoice")
>>> invoices.query(
...     columns="$number, $total, @rows.aggr(count())",
...     order_by="$id", limit=3,
... ).fetch()
[[number=INV-001,total=34.9,_rows_count=3], [number=INV-002,total=48.7,_rows_count=3], [number=INV-003,total=25.3,_rows_count=3]]
>>> db.commit()

Scene 06

Average the selling prices of each product

Let's average the recorded selling prices. Follow a product's invoice rows and average their unit prices. This is one price per row, without weighting by quantity.

Example & screen notes
>>> 
>>> ######## Average the recorded selling prices ########
>>> 
>>> products.query(
...     columns="$code, @rows.aggr(count(), avg($unit_price))",
...     where="$code IN :codes", codes=["TEA", "COF", "MUG"],
...     order_by="$code",
... ).fetch()
[[code=COF,_rows_count=2,_rows_unit_price_avg=6.9], [code=MUG,_rows_count=2,_rows_unit_price_avg=5.25], [code=TEA,_rows_count=2,_rows_unit_price_avg=3.5]]
>>> db.commit()

Scene 07

Filter products by their average selling price

Let's name the average price so we can filter it. Define the average in the model. In this release, aggr returns columns; a named formula also works in where and order by. Let's filter by average price. Choose three twenty to four eighty, passing both bounds as parameters. Between includes the endpoints. Sort by average price, and read the six matching products.

Example & screen notes
>>> 
>>> ######## Name the average price so we can filter it ########
>>> 
>>> _ = product.formulaColumn(
...     "average_price", dtype="N",
...     select=dict(
...         table="shop.invoice_row", columns="AVG($unit_price)",
...         where="$product_code = #THIS.code",
...     ),
... )
>>> db.reloadModel(model)
>>> products = db.table("shop.product")
>>> rows = db.table("shop.invoice_row")

>>> 
>>> ######## Filter by average price ########
>>> 
>>> products.query(
...     columns="$code, $name, $average_price",
...     where="$average_price BETWEEN :min_price AND :max_price",
...     min_price=3.2, max_price=4.8,
...     order_by="$average_price, $code",
... ).fetch()
[[code=BTEA,name=Black tea,average_price=3.2], [code=TEA,name=Green tea,average_price=3.5], [code=CHO,name=Dark chocolate,average_price=3.9], [code=HTEA,name=Herbal tea,average_price=4.1], [code=JAM,name=Strawberry jam,average_price=4.6], [code=STR,name=Tea strainer,average_price=4.8]]
>>> db.commit()

Scene 08

Thank you — next steps

Thanks for following along! We summarized related records while keeping one result per parent. Next, group by will combine sales rows into reports. We will use where and having to filter before and after grouping.