Asqueel

Course / Orientation / Lesson 06

Lesson 06 · Orientation

Group by and HAVING

Summarize sales by city with GROUP BY, SUM and COUNT; distinguish WHERE filters on rows from HAVING filters on groups.

1:19 · video4 scenesPrerequisites: the in-memory Python session from lesson 5

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 LESSON1:19 · 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 five.

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. Turn invoice rows into one sales summary per city. Then filter individual rows with where and whole groups with having. We will focus on building the query; result formats and file exports have their own next lesson.

Scene 02

Group invoice rows by customer city

Let's total sales by city. City is the alias from lesson four. Group by puts rows from the same city together. Sum adds their calculated amounts. We get one summary per city, not one result per invoice row. Let's name the report columns. We just ran without aliases. Here, optional AS labels make the report easier to read: sales and row count. Count measures invoice rows, not customers or units sold. The database still holds all thirty-six original rows.

Example & screen notes
>>> 
>>> ######## Total sales by city ########
>>> 
>>> rows = db.table("shop.invoice_row")
>>> products = db.table("shop.product")
>>> rows.query(
...     columns="$city, SUM($calculated_total)",
...     group_by="$city", order_by="$city", limit=3,
... ).fetch()
[[city=Amsterdam,SUM__calculated_total_=26.2], [city=Boston,SUM__calculated_total_=57.8], [city=Florence,SUM__calculated_total_=300.65000000000003]]

>>> 
>>> ######## Name the report columns ########
>>> 
>>> report = rows.query(
...     columns="$city, ROUND(SUM($calculated_total), 2) AS sales, "
...             "COUNT(*) AS row_count",
...     group_by="$city", order_by="$city",
... )
>>> report.fetch()
[[city=Amsterdam,sales=26.2,row_count=3], [city=Boston,sales=57.8,row_count=3], [city=Florence,sales=300.65,row_count=12], [city=Helsinki,sales=57.2,row_count=6], [city=London,sales=189.7,row_count=6], [city=Manchester,sales=87.4,row_count=3], [city=New York,sales=96.3,row_count=3]]

Scene 03

Filter rows first and groups afterwards

Let's filter rows and groups. Where keeps individual rows priced at least five before grouping. Having then keeps cities whose remaining sales reach one hundred. Florence and London qualify. Both thresholds are parameters.

Example & screen notes
>>> 
>>> ######## Filter rows and groups ########
>>> 
>>> selected = rows.query(
...     columns="$city, ROUND(SUM($calculated_total), 2) AS sales",
...     where="$unit_price >= :min_price", min_price=5,
...     group_by="$city",
...     having="SUM($calculated_total) >= :min_sales", min_sales=100,
...     order_by="$city",
... )
>>> selected.fetch()
[[city=Florence,sales=278.65], [city=London,sales=156.6]]
>>> db.commit()

Scene 04

Thank you — from rows to groups

Thanks for following along! We turned detail rows into city summaries, then separated filtering before and after grouping. Keep this report query ready. In the next lesson, we will choose how to fetch its results in Python and export them to files.