Sum of products of columns in polars

I have a dataset, part of which looks like this:

What I want is a list of unique customer, product pairs with the total sales, so something like:

Here's my attempt at constructing this. This gives me the grand total of all sales, which isn't what I want. What's a correct approach?

import polars as pl

db.select(
    (
        pl.col('customer'),
        pl.col('product'),
        pl.col('quantity').mul(pl.col('price')).alias('total')
    )
).group_by(('customer', 'product'))

To do this calculate the sale amount for each row then group by both customer and product columns, and then sum the calculated amounts within each group

Your current query has a few issues:

• You're selecting product and customer but grouping by item_lookup_key and shopper_card_number
• You need to use an aggregation function after grouping

This approach works:

db.group_by(["customer", "product"]).agg([
    ((pl.col("quantity") * pl.col("price")).sum()).alias("total")
])

A more concise alternative is the expr.dot:

db.group_by("customer", "product").agg(
    total=pl.col("quantity").dot("price")
)