Totals from one query

Task: show sales totals by region without asking the database twice.

# Recipe: one query to the database, and totals computed from its result.
paths:
  components: ./components
  migrations: ./migrations

datasources:
  db:
    driver: sqlite
    database: ./data/sales.db

CREATE TABLE sales (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    region TEXT NOT NULL,
    amount INTEGER NOT NULL
);

INSERT INTO sales (region, amount) VALUES
    ('North', 120), ('South', 80), ('North', 200), ('East', 50), ('South', 40);

q:query source="sales" runs its SQL over the result of the query named sales, which appears as a table of that name. queries="1" in the test checks that the database was asked only once.

<q:component name="Sales">
  <!-- The database is asked once. -->
  <q:query name="sales" datasource="db">
    SELECT region, amount FROM sales ORDER BY id
  </q:query>

  <!-- source="sales": SQL over that result, in memory — the table is
       named after the query. -->
  <q:query name="totals" source="sales">
    SELECT region, SUM(amount) AS total, COUNT(*) AS n
    FROM sales GROUP BY region ORDER BY total DESC
  </q:query>

  <ui:window title="Sales">
    <ui:table source="{totals}">
      <ui:column key="region" label="Region" />
      <ui:column key="total" label="Total" />
      <ui:column key="n" label="Sales" />
    </ui:table>
    <ui:text>{sales_result.recordCount} sales</ui:text>
  </ui:window>
</q:component>

<q:test name="totals by region, from one query" page="/">
  <test:visit />
  <test:expect text="North 320 2 South 120 2 East 50 1" />
  <test:expect text="5 sales" />
  <test:expect queries="1" />
</q:test>

tests/totals.test.q
  PASS  totals by region, from one query
1 passed, 0 failed

See DB-3.