# 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 November 19, 2026

By Gregor Dzierzon

## 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:

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

```text
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:

```python
print(conn.execute("SELECT COUNT(*), SUM(price), AVG(price) FROM products").fetchone())
```

```text
(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:

```python
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)
```

```text
('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:

```python
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)}")
```

```text
[('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:

```text
(['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

- [Aggregate Function](https://www.ask-codey.com/glossary/aggregate-function)
- [COUNT](https://www.ask-codey.com/glossary/count)
- [GROUP BY](https://www.ask-codey.com/glossary/group-by)
- [HAVING](https://www.ask-codey.com/glossary/having)
- [OFFSET Clause](https://www.ask-codey.com/glossary/offset-clause)
- [Pagination](https://www.ask-codey.com/glossary/pagination)

## Related

- [Joining Tables](https://www.ask-codey.com/lessons/fastapi-sql-joins)
- [Indexes and Unique Values](https://www.ask-codey.com/lessons/fastapi-sql-indexes-and-unique)
- [Reading Rows: SELECT, WHERE and ORDER BY](https://www.ask-codey.com/lessons/fastapi-sql-select)

Source: https://www.ask-codey.com/lessons/fastapi-sql-counting-and-paging
