Counting, Grouping and Paging

COUNT, SUM and AVG let the database add up rows instead of sending every one to Python, GROUP BY gives one answer for each vendor, and LIMIT with OFFSET hands an API one page of a long list at a time, with COUNT(*) giving the total beside it.

Journeyman · SQLite · SQL · Database · Updated

By

After this lesson you can

  • Count, add up and average rows with COUNT, SUM and AVG, give one answer per group with GROUP BY, and filter the groups with HAVING
  • Return one page of a long list with ORDER BY, LIMIT and OFFSET, work out the offset from a page number, and give the total count beside the page

Key takeaways

  • An aggregate function such as COUNT, SUM or AVG turns many rows into one answer, in the database, without sending every row to Python.
  • GROUP BY gives one answer for each group, such as each vendor's number of products. HAVING keeps only the groups that pass a test.
  • After a LEFT JOIN, COUNT(p.id) counts only real matches, so a vendor with no products gets 0, while COUNT(*) would count its empty row as 1.
  • LIMIT takes a page's worth of rows and OFFSET skips the pages before it. Page 3 of size 10 starts after (3 - 1) * 10 = 20 rows.
  • An API sends long lists one page at a time, sorted with ORDER BY so no row is repeated or skipped, with the total count so the app can show "page 2 of 3".

"12 items in your cart, $84.50 in all." "Showing 21 to 40 of 1,283 results." Screens like these are everywhere, and behind each one is an API asking its database to count, add up, or hand over one page of a long list. This lesson asks those questions of the Saturday Farmers Market's database.

It's the database from Joining Tables, with one more vendor: Riverbend Bakery, at stall 9, which sells a sourdough loaf for $6.50. That makes five vendors and six products.

Let the database do the counting

The market's front page says how many products are on sale and what they cost on average. A first try reads every row into Python and counts them there:

rows = conn.execute("SELECT price FROM products").fetchall()
print(len(rows), "products")
6 products

It's right, but every row traveled from the database to Python only to be counted. With six rows that costs nothing; with sixty thousand it's a slow page. The database can do the arithmetic where the rows already are:

print(conn.execute("SELECT COUNT(*), SUM(price), AVG(price) FROM products").fetchone())
(6, 39.0, 6.5)

COUNT(*) (read "count star") counts the rows, SUM(price) adds up the prices and AVG(price) averages them, and only that one row of answers comes back. Each is an aggregate function: many rows go in, one value comes out. MIN and MAX work the same way.

One answer per group: GROUP BY

The vendor directory shows each vendor's number of products and what a basket with one of each would cost. That's one answer per vendor, and GROUP BY makes the groups. It's joined with LEFT JOIN, as in Joining Tables, so Pepper Patch, which sells nothing yet, still gets a row:

for row in conn.execute("""
    SELECT v.name, COUNT(p.id) AS products, SUM(p.price) AS basket
    FROM vendors AS v
    LEFT JOIN products AS p ON p.vendor_id = v.id
    GROUP BY v.id
    ORDER BY v.stall
"""):
    print(row)
('Green Basket', 1, 3.0)
('Sunny Acres', 2, 8.5)
('Hilltop Honey', 2, 21.0)
('Riverbend Bakery', 1, 6.5)
('Pepper Patch', 0, None)

GROUP BY v.id puts each vendor's rows in a pile of their own, and the aggregates run once per pile. Look at COUNT(p.id): it counts the rows where p.id isn't NULL. Pepper Patch's only row from the LEFT JOIN has NULL for every product column, so it counts 0. COUNT(*) counts rows of any kind and would say 1, a product that doesn't exist. SUM of no prices is NULL, which Python reads as None.

To keep only some groups, add HAVING after GROUP BY: HAVING COUNT(p.id) >= 2 keeps Sunny Acres and Hilltop Honey. WHERE picks rows before they're grouped; HAVING picks groups after they're counted.

Paging: LIMIT and OFFSET

By midsummer the market lists four hundred products, and the app on someone's phone asks GET /products. Answering with all four hundred is slow to send, slow to show, and gets slower every week the market grows. So APIs answer long lists one page at a time, and the app asks for the next page when it's needed. That's pagination, or paging.

LIMIT (taught in Reading Rows: SELECT, WHERE and ORDER BY) takes the first rows, and OFFSET skips rows before it starts. Page 1 skips none, page 2 skips one page's worth, page 3 skips two:

page size offset = (page - 1) * size rows it gives
1 2 0 1st and 2nd
2 2 2 3rd and 4th
3 2 4 5th and 6th

A page is only useful beside the total, so the app can show "page 2 of 3". The same function asks for both:

def product_page(conn, page, size):
    offset = (page - 1) * size
    rows = conn.execute(
        "SELECT name, price FROM products ORDER BY name LIMIT ? OFFSET ?",
        (size, offset),
    ).fetchall()
    total = conn.execute("SELECT COUNT(*) FROM products").fetchone()[0]
    return rows, total


rows, total = product_page(conn, 2, 2)
print(rows)
print(f"Page 2 of {math.ceil(total / 2)}")
[('Snap peas', 3.5), ('Sourdough loaf', 6.5)]
Page 2 of 3

math.ceil rounds up (it needs import math), so six products at two a page make three pages, and seven would make four. The ORDER BY matters: without it, SQLite may hand back rows in any order, and a product could turn up on two pages or on none. A page past the end gives an empty list, which the app reads as "no more".

Do it: page through the vendor directory

The directory page on the market's app shows a few vendors at a time, in stall order, and the total so it can say how many pages there are.

  • conn: a connection to the market's database. The vendors table has the columns id, name and stall.
  • page: the page number, starting at 1.
  • size: how many vendors go on a page.

Your job: write vendor_page(conn, page, size). It returns a tuple of two things: a list of the vendor names on that page, and the number of vendors in the whole table.

  • Sort the vendors by stall, lowest first.
  • Page 1 is the first size vendors, page 2 the next size, and so on.
  • A page past the last vendor gives an empty list, with the total still filled in.

Worked through: the five vendors in stall order are Green Basket (2), Sunny Acres (4), Hilltop Honey (7), Riverbend Bakery (9) and Pepper Patch (12). vendor_page(conn, 2, 2) skips (2 - 1) * 2 = 2 vendors and takes the next 2, so it returns:

(['Hilltop Honey', 'Riverbend Bakery'], 5)

Practiced live on the lesson page: write the Python right there and check it.

Check yourself

2 questions to try on the lesson page.

So what

Counting and paging in the database is what keeps an API fast as its data grows: the screen gets the numbers and the one page it needs, never every row.

Key terms

Related