{
  "nbformat": 4,
  "nbformat_minor": 5,
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Group by and HAVING\n",
        "\n",
        "Summarize sales by city with GROUP BY, SUM and COUNT; distinguish WHERE filters on rows from HAVING filters on groups.\n",
        "\n",
        "[Watch lesson 6](https://asqueel.org/learn/05-relations/)\n",
        "\n",
        "**Run the cells from top to bottom in a fresh Python kernel.** Each notebook includes its own starting state. SQLite runs in memory; restarting the kernel discards the database. To repeat the exercise, restart the kernel and run all cells. Colab execution requires a Google account.\n",
        "\n",
        "\u00a9 2026 Softwell S.r.l. \u00b7 Apache-2.0"
      ],
      "id": "l6-c0-dd72cd602800"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Before the examples: `_ = ...` is optional\n",
        "\n",
        "It assigns a return value we do not need, preventing its automatic display in the Python console or notebook. The call still runs normally. This is a Python convention, not an Asqueel requirement. When you need the result, use a meaningful variable name."
      ],
      "id": "l6-c1-5261ce0eb0bf"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Prepare the notebook\n",
        "\n",
        "The video starts in a terminal. Here, install the same official package in the notebook kernel. No virtual environment or separate Python console is needed in Colab."
      ],
      "id": "l6-c2-a53efa980848"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%pip install -q \"asqueel[migration]==0.6.0\""
      ],
      "id": "l6-c3-5283bc5543e7",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%config InteractiveShell.ast_node_interactivity = \"all\""
      ],
      "id": "l6-c4-7a5eb4f63b61",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Recreate the starting point\n",
        "\n",
        "The previous lessons are rebuilt below. The four fictional CSV files are included in this notebook and written into a new temporary folder. No downloads or existing database are needed."
      ],
      "id": "l6-c5-26bce07fb451"
    },
    {
      "cell_type": "code",
      "metadata": {
        "jupyter": {
          "source_hidden": true
        },
        "cellView": "form"
      },
      "source": [
        "#@title Prepare the sample CSV files\n",
        "from pathlib import Path\n",
        "import os\n",
        "import tempfile\n",
        "_lesson_folder = tempfile.TemporaryDirectory(prefix=\"asqueel-notebook-\")\n",
        "os.chdir(_lesson_folder.name)\n",
        "_csv_files = {'customers.csv': 'id,name,city\\n1,Ada,Florence\\n2,Grace,London\\n3,Linus,Helsinki\\n4,Guido,Amsterdam\\n5,Elena,Florence\\n6,Alan,Manchester\\n7,Margaret,New York\\n8,Barbara,Boston\\n', 'invoice_rows.csv': 'id,invoice_id,product_code,quantity,unit_price,total\\n1,101,TEA,2,3.50,7.00\\n2,101,COF,1,6.90,6.90\\n3,101,MUG,4,5.25,21.00\\n4,102,BTEA,2,3.20,6.40\\n5,102,HTEA,3,4.10,12.30\\n6,102,HON,4,7.50,30.00\\n7,103,SUG,3,2.30,6.90\\n8,103,CHO,4,3.90,15.60\\n9,103,BIS,1,2.80,2.80\\n10,104,JAM,4,4.60,18.40\\n11,104,OAT,1,2.40,2.40\\n12,104,JUI,2,2.70,5.40\\n13,105,WAT,1,0.90,0.90\\n14,105,LEM,2,1.80,3.60\\n15,105,KET,3,32.90,98.70\\n16,106,POT,2,18.50,37.00\\n17,106,PLT,3,6.40,19.20\\n18,106,BWL,4,7.80,31.20\\n19,107,SPO,3,9.50,28.50\\n20,107,TRY,4,14.90,59.60\\n21,107,JAR,1,8.20,8.20\\n22,108,NAP,4,12.00,48.00\\n23,108,TWL,1,5.60,5.60\\n24,108,FLT,2,2.10,4.20\\n25,109,TIN,1,6.70,6.70\\n26,109,MAT,2,11.40,22.80\\n27,109,CAF,3,29.90,89.70\\n28,110,BOT,2,13.50,27.00\\n29,110,STR,3,4.80,14.40\\n30,110,SCL,4,24.90,99.60\\n31,111,TEA,3,3.50,10.50\\n32,111,COF,4,6.90,27.60\\n33,111,MUG,1,5.25,5.25\\n34,112,BTEA,4,3.20,12.80\\n35,112,HTEA,1,4.10,4.10\\n36,112,HON,2,7.50,15.00\\n', 'invoices.csv': 'id,number,customer_id,invoice_date\\n101,INV-001,1,2026-09-28\\n102,INV-002,2,2026-09-29\\n103,INV-003,3,2026-09-30\\n104,INV-004,4,2026-10-01\\n105,INV-005,5,2026-10-02\\n106,INV-006,6,2026-10-03\\n107,INV-007,7,2026-10-04\\n108,INV-008,8,2026-10-05\\n109,INV-009,1,2026-10-06\\n110,INV-010,2,2026-10-07\\n111,INV-011,5,2026-10-08\\n112,INV-012,3,2026-10-09\\n', 'products.csv': 'code,name,category,stock\\nBTEA,Black tea,Drinks,30\\nHTEA,Herbal tea,Drinks,18\\nHON,Honey,Food,22\\nSUG,Cane sugar,Food,40\\nCHO,Dark chocolate,Food,35\\nBIS,Biscuits,Food,28\\nJAM,Strawberry jam,Food,16\\nOAT,Oat drink,Drinks,32\\nJUI,Apple juice,Drinks,26\\nWAT,Mineral water,Drinks,60\\nLEM,Lemonade,Drinks,30\\nKET,Kettle,Kitchen,9\\nPOT,Teapot,Kitchen,14\\nPLT,Plate,Kitchen,36\\nBWL,Bowl,Kitchen,24\\nSPO,Spoon set,Kitchen,20\\nTRY,Serving tray,Kitchen,11\\nJAR,Storage jar,Kitchen,25\\nNAP,Cotton napkins,Kitchen,18\\nTWL,Tea towel,Kitchen,30\\nFLT,Coffee filters,Kitchen,45\\nTIN,Tea tin,Kitchen,15\\nMAT,Table mats,Kitchen,16\\nCAF,Coffee maker,Kitchen,7\\nBOT,Water bottle,Kitchen,21\\nSTR,Tea strainer,Kitchen,23\\nSCL,Kitchen scale,Kitchen,8\\n'}\n",
        "for _name, _text in _csv_files.items():\n",
        "    _ = Path(_name).write_text(_text, encoding=\"utf-8\")\n"
      ],
      "id": "l6-c6-89efb228754e",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "jupyter": {
          "source_hidden": true
        },
        "cellView": "form"
      },
      "source": [
        "#@title Recreate the previous lessons\n",
        "\n",
        "######## Import our two tools ########\n",
        "\n",
        "from asqueel import AsqueelDb, SqlDatabaseConfig\n",
        "\n",
        "\n",
        "######## Choose SQLite in memory ########\n",
        "\n",
        "model = SqlDatabaseConfig()\n",
        "root = model.source.db(implementation=\"sqlite\")\n",
        "_ = root.connection(name=\":memory:\", implementation=\"sqlite\")\n",
        "\n",
        "\n",
        "######## Describe a product table ########\n",
        "\n",
        "shop = root.packages().package(\"shop\", sqlschema=\"shop\")\n",
        "product = shop.table(\"product\", pkey=\"code\")\n",
        "_ = product.column(\"code\", size=\":10\")\n",
        "_ = product.column(\"name\", size=\":60\")\n",
        "\n",
        "\n",
        "######## Create that table ########\n",
        "\n",
        "db = AsqueelDb(model)\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## Add our first product ########\n",
        "\n",
        "products = db.table(\"shop.product\")\n",
        "products.insert({\"code\": \"TEA\", \"name\": \"Tea\"})\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Read our product ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()\n",
        "\n",
        "\n",
        "######## Change its name ########\n",
        "\n",
        "products.update({\"name\": \"Green tea\"}, pkey=\"TEA\")\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Check the update ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Track category and stock ########\n",
        "\n",
        "_ = product.column(\"category\", size=\":30\")\n",
        "_ = product.column(\"stock\", dtype=\"I\")\n",
        "\n",
        "\n",
        "######## Load the updated model ########\n",
        "\n",
        "db.reloadModel(model)\n",
        "\n",
        "\n",
        "######## Add those database columns ########\n",
        "\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## Check our original row ########\n",
        "\n",
        "products = db.table(\"shop.product\")\n",
        "products.query(columns=\"$name, $stock\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Give tea its stock ########\n",
        "\n",
        "products.update({\"category\": \"Drinks\", \"stock\": 8}, pkey=\"TEA\")\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Read the new values ########\n",
        "\n",
        "products.query(columns=\"$name, $category, $stock\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Add products from CSV ########\n",
        "\n",
        "from io import StringIO\n",
        "data = StringIO(\n",
        "    \"code,name,category,stock\\n\"\n",
        "    \"COF,Coffee,Drinks,24\\n\"\n",
        "    \"MUG,Mug,Kitchen,12\\n\"\n",
        ")\n",
        "\n",
        "\n",
        "######## Import those rows ########\n",
        "\n",
        "products.sql_insert(data, format=\"csv\")\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## List just the drinks ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$name, $stock\",\n",
        "    where=\"$category = :category\", category=\"Drinks\",\n",
        "    order_by=\"$stock DESC\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "# Continue with lesson two.\n",
        "\n",
        "######## Check our starting point ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Describe our customers ########\n",
        "\n",
        "customer = shop.table(\"customer\", pkey=\"id\")\n",
        "_ = customer.column(\"id\", dtype=\"I\")\n",
        "_ = customer.column(\"name\", size=\":60\")\n",
        "_ = customer.column(\"city\", size=\":60\")\n",
        "\n",
        "\n",
        "######## Create the customer table ########\n",
        "\n",
        "db.reloadModel(model)\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## Describe a dated invoice ########\n",
        "\n",
        "invoice = shop.table(\"invoice\", pkey=\"id\")\n",
        "_ = invoice.column(\"id\", dtype=\"I\")\n",
        "_ = invoice.column(\"number\", size=\":20\")\n",
        "_ = invoice.column(\"invoice_date\", dtype=\"D\")\n",
        "\n",
        "\n",
        "######## Connect invoices to customers ########\n",
        "\n",
        "customer_id = invoice.column(\"customer_id\", dtype=\"I\")\n",
        "_ = customer_id.relation(\n",
        "    \"shop.customer.id\", foreign_key=True, relation_name=\"invoices\",\n",
        ")\n",
        "\n",
        "\n",
        "######## Describe an invoice row ########\n",
        "\n",
        "invoice_row = shop.table(\"invoice_row\", pkey=\"id\")\n",
        "_ = invoice_row.column(\"id\", dtype=\"I\")\n",
        "_ = invoice_row.column(\"quantity\", dtype=\"I\")\n",
        "_ = invoice_row.column(\"unit_price\", dtype=\"N\", size=\"12,2\")\n",
        "_ = invoice_row.column(\"total\", dtype=\"N\", size=\"12,2\")\n",
        "\n",
        "\n",
        "######## Connect the row at both ends ########\n",
        "\n",
        "invoice_id = invoice_row.column(\"invoice_id\", dtype=\"I\")\n",
        "_ = invoice_id.relation(\n",
        "    \"shop.invoice.id\", foreign_key=True, relation_name=\"rows\",\n",
        ")\n",
        "product_code = invoice_row.column(\"product_code\", size=\":10\")\n",
        "_ = product_code.relation(\n",
        "    \"shop.product.code\", foreign_key=True, relation_name=\"rows\",\n",
        ")\n",
        "\n",
        "\n",
        "######## Apply the new model ########\n",
        "\n",
        "db.reloadModel(model)\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## Get fresh table handles ########\n",
        "\n",
        "products = db.table(\"shop.product\")\n",
        "customers = db.table(\"shop.customer\")\n",
        "invoices = db.table(\"shop.invoice\")\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Inspect the row file ########\n",
        "\n",
        "from pathlib import Path\n",
        "print(\"\\n\".join(Path(\"invoice_rows.csv\").read_text().splitlines()[:4]))\n",
        "\n",
        "\n",
        "######## Expand the product catalogue ########\n",
        "\n",
        "with open(\"products.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    products.sql_insert(source)\n",
        "\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Count the catalogue ########\n",
        "\n",
        "products.query().count()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Import customers and their cities ########\n",
        "\n",
        "with open(\"customers.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    customers.sql_insert(source)\n",
        "\n",
        "db.commit()\n",
        "customers.query(columns=\"$name, $city\", limit=3).fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Import the invoices ########\n",
        "\n",
        "with open(\"invoices.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    invoices.sql_insert(source)\n",
        "\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Import the invoice rows ########\n",
        "\n",
        "with open(\"invoice_rows.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    rows.sql_insert(source)\n",
        "\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Check how much we imported ########\n",
        "\n",
        "invoices.query().count()\n",
        "rows.query().count()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Read invoices with their customers ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number, $invoice_date, \"\n",
        "            \"@customer_id.name, @customer_id.city\",\n",
        "    limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Inspect the first invoice ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@product_code.name, \"\n",
        "            \"$quantity, $unit_price, $total\",\n",
        "    where=\"$invoice_id = :invoice\", invoice=101,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Read across both connections ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@invoice_id.number, \"\n",
        "            \"@invoice_id.@customer_id.city, \"\n",
        "            \"@product_code.name, $total\",\n",
        "    limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "# Continue with lesson three.\n",
        "\n",
        "######## Find invoices for Florence ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number\",\n",
        "    where=\"@customer_id.city = :city\", city=\"Florence\",\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Sort invoices by city ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number, @customer_id.city\",\n",
        "    order_by=\"@customer_id.city\", limit=4,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Show the newest rows first ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@invoice_id.invoice_date, $total\",\n",
        "    order_by=\"@invoice_id.invoice_date DESC\", limit=3,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Find rows for Florence ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@product_code.name, $quantity\",\n",
        "    where=\"@invoice_id.@customer_id.city = :city\",\n",
        "    city=\"Florence\", limit=3,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Combine the conditions ########\n",
        "\n",
        "from datetime import date\n",
        "rows.query(\n",
        "    columns=\"@product_code.name, $total\",\n",
        "    where=\"@invoice_id.@customer_id.city = :city \"\n",
        "          \"AND @invoice_id.invoice_date >= :since\",\n",
        "    city=\"Florence\", since=date(2026, 10, 1),\n",
        "    order_by=\"@invoice_id.invoice_date DESC\", limit=4,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Close this read transaction ########\n",
        "\n",
        "db.commit()\n",
        "\n",
        "# Continue with lesson 4.\n",
        "\n",
        "######## Call this path city ########\n",
        "\n",
        "_ = invoice_row.aliasColumn(\n",
        "    \"city\", relation_path=\"@invoice_id.@customer_id.city\",\n",
        ")\n",
        "db.reloadModel(model)\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Read and filter with that name ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"$city, @product_code.name\",\n",
        "    where=\"$city = :city\", city=\"Florence\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Calculate quantity times price ########\n",
        "\n",
        "_ = invoice_row.formulaColumn(\n",
        "    \"calculated_total\",\n",
        "    sql_formula=\"ROUND($quantity * $unit_price, 2)\",\n",
        "    dtype=\"N\",\n",
        ")\n",
        "db.reloadModel(model)\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Read the calculated amounts ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"$quantity, $unit_price, $calculated_total\",\n",
        "    where=\"$invoice_id = :invoice\", invoice=101,\n",
        "    order_by=\"$id\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Check whether the database needs changing ########\n",
        "\n",
        "db.migration_plan().empty\n",
        "\n",
        "\n",
        "######## Temporarily change the quantity to three ########\n",
        "\n",
        "rows.update({\"quantity\": 3}, pkey=1)\n",
        "rows.query(\n",
        "    columns=\"$quantity, $total, $calculated_total\",\n",
        "    where=\"$id = :id\", id=1,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Undo that change ########\n",
        "\n",
        "db.rollback()\n",
        "\n",
        "# Continue with lesson 5.\n",
        "\n",
        "######## Give each invoice a calculated total ########\n",
        "\n",
        "_ = invoice.formulaColumn(\n",
        "    \"total\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice_row\",\n",
        "        columns=\"ROUND(SUM($calculated_total), 2)\",\n",
        "        where=\"$invoice_id = #THIS.id\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "invoices = db.table(\"shop.invoice\")\n",
        "\n",
        "\n",
        "######## Read those totals as ordinary fields ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number, $total\", order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Total the invoices of each customer ########\n",
        "\n",
        "_ = customer.formulaColumn(\n",
        "    \"total\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice\",\n",
        "        columns=\"ROUND(SUM($total), 2)\",\n",
        "        where=\"$customer_id = #THIS.id\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "customers = db.table(\"shop.customer\")\n",
        "\n",
        "\n",
        "######## Read each customer's sales total ########\n",
        "\n",
        "customers.query(\n",
        "    columns=\"$name, $total\", order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Total the sales of each product ########\n",
        "\n",
        "_ = product.formulaColumn(\n",
        "    \"total\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice_row\",\n",
        "        columns=\"ROUND(SUM($calculated_total), 2)\",\n",
        "        where=\"$product_code = #THIS.code\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "products = db.table(\"shop.product\")\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Read the product totals ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$code, $name, $total\",\n",
        "    where=\"$code IN :codes\", codes=[\"TEA\", \"COF\", \"MUG\"],\n",
        "    order_by=\"$code\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Summarize each customer's invoices ########\n",
        "\n",
        "customers = db.table(\"shop.customer\")\n",
        "customers.query(\n",
        "    columns=\"$name, @invoices.aggr(\"\n",
        "            \"sum($total), count(), avg($total))\",\n",
        "    order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Count the rows of each invoice too ########\n",
        "\n",
        "invoices = db.table(\"shop.invoice\")\n",
        "invoices.query(\n",
        "    columns=\"$number, $total, @rows.aggr(count())\",\n",
        "    order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Average the recorded selling prices ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$code, @rows.aggr(count(), avg($unit_price))\",\n",
        "    where=\"$code IN :codes\", codes=[\"TEA\", \"COF\", \"MUG\"],\n",
        "    order_by=\"$code\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Name the average price so we can filter it ########\n",
        "\n",
        "_ = product.formulaColumn(\n",
        "    \"average_price\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice_row\", columns=\"AVG($unit_price)\",\n",
        "        where=\"$product_code = #THIS.code\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "products = db.table(\"shop.product\")\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Filter by average price ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$code, $name, $average_price\",\n",
        "    where=\"$average_price BETWEEN :min_price AND :max_price\",\n",
        "    min_price=3.2, max_price=4.8,\n",
        "    order_by=\"$average_price, $code\",\n",
        ").fetch()\n",
        "db.commit()\n"
      ],
      "id": "l6-c7-5a1b50bfe2ee",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 1 \u2014 What we'll learn in this lesson"
      ],
      "id": "l6-c8-0faae73bfca2"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "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."
      ],
      "id": "l6-c9-655fa480fb1a"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "What we'll learn in this lesson\n",
        "GROUP BY \u2192 one sales summary per city\n",
        "SUM and COUNT \u2192 totals and row counts\n",
        "WHERE \u2192 filter before grouping\n",
        "HAVING \u2192 filter after grouping\n",
        "\n",
        "Same SQLite database in memory\n",
        "```"
      ],
      "id": "l6-c10-8041e3aaf44e"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 2 \u2014 Group invoice rows by customer city"
      ],
      "id": "l6-c11-d637a84b37f6"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "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."
      ],
      "id": "l6-c12-0c14e9a2e60f"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Total sales by city ########\n",
        "\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "products = db.table(\"shop.product\")\n",
        "rows.query(\n",
        "    columns=\"$city, SUM($calculated_total)\",\n",
        "    group_by=\"$city\", order_by=\"$city\", limit=3,\n",
        ").fetch()"
      ],
      "id": "l6-c13-575abf7b1575",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "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."
      ],
      "id": "l6-c14-036cf64c3109"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Name the report columns ########\n",
        "\n",
        "report = rows.query(\n",
        "    columns=\"$city, ROUND(SUM($calculated_total), 2) AS sales, \"\n",
        "            \"COUNT(*) AS row_count\",\n",
        "    group_by=\"$city\", order_by=\"$city\",\n",
        ")\n",
        "report.fetch()"
      ],
      "id": "l6-c15-c8a18394a7f7",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 3 \u2014 Filter rows first and groups afterwards"
      ],
      "id": "l6-c16-6968ca07f044"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "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."
      ],
      "id": "l6-c17-bdbf0bd5226e"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Filter rows and groups ########\n",
        "\n",
        "selected = rows.query(\n",
        "    columns=\"$city, ROUND(SUM($calculated_total), 2) AS sales\",\n",
        "    where=\"$unit_price >= :min_price\", min_price=5,\n",
        "    group_by=\"$city\",\n",
        "    having=\"SUM($calculated_total) >= :min_sales\", min_sales=100,\n",
        "    order_by=\"$city\",\n",
        ")\n",
        "selected.fetch()\n",
        "db.commit()"
      ],
      "id": "l6-c18-57992574288d",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 4 \u2014 Thank you \u2014 from rows to groups"
      ],
      "id": "l6-c19-d3e7c428ef20"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "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."
      ],
      "id": "l6-c20-9a9a48df5847"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "Thank you!\n",
        "GROUP BY \u2192 combine rows by city\n",
        "SUM / COUNT \u2192 summarize each group\n",
        "WHERE \u2192 input rows\n",
        "HAVING \u2192 grouped results\n",
        "\n",
        "Next: fetch and export\n",
        "```"
      ],
      "id": "l6-c21-e28fdeac27a6"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Check your result\n",
        "\n",
        "Run this final cell to check the exercise."
      ],
      "id": "l6-c22-062cfb81c9cc"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from math import isclose\n",
        "expected = {'Amsterdam': {'sales': 26.2, 'row_count': 3, 'ids': [10, 11, 12]}, 'Boston': {'sales': 57.8, 'row_count': 3, 'ids': [22, 23, 24]}, 'Florence': {'sales': 300.65, 'row_count': 12, 'ids': [1, 2, 3, 13, 14, 15, 25, 26, 27, 31, 32, 33]}, 'Helsinki': {'sales': 57.2, 'row_count': 6, 'ids': [7, 8, 9, 34, 35, 36]}, 'London': {'sales': 189.7, 'row_count': 6, 'ids': [4, 5, 6, 28, 29, 30]}, 'Manchester': {'sales': 87.4, 'row_count': 3, 'ids': [16, 17, 18]}, 'New York': {'sales': 96.3, 'row_count': 3, 'ids': [19, 20, 21]}}\n",
        "expected_filtered = {'Florence': 278.65, 'London': 156.6}\n",
        "records = report.fetch()\n",
        "assert [r[\"city\"] for r in records] == sorted(expected)\n",
        "assert all(isclose(float(r[\"sales\"]), expected[r[\"city\"]][\"sales\"]) and r[\"row_count\"] == expected[r[\"city\"]][\"row_count\"] for r in records)\n",
        "selected_rows = selected.fetch()\n",
        "assert [r[\"city\"] for r in selected_rows] == sorted(expected_filtered)\n",
        "assert all(isclose(float(r[\"sales\"]), expected_filtered[r[\"city\"]]) for r in selected_rows)\n",
        "assert not rows.query(columns=\"$city, SUM($calculated_total)\", group_by=\"$city\", having=\"SUM($calculated_total) > :minimum\", minimum=10000).fetch()\n",
        "assert db.table(\"shop.product\").query().count() == 30\n",
        "assert db.table(\"shop.customer\").query().count() == 8\n",
        "assert db.table(\"shop.invoice\").query().count() == 12\n",
        "assert rows.query().count() == 36\n",
        "db.commit()\n",
        "\n",
        "db.commit()\n",
        "db.commit()\n",
        "print(\"Lesson checks passed.\")\n"
      ],
      "id": "l6-c23-5173e62e65af",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3",
      "language": "python"
    },
    "language_info": {
      "name": "python"
    },
    "asqueel": {
      "version": "0.6.0",
      "lesson": 6
    }
  }
}