More sophisticated aggregate queries

Using Pony you can do even more complex grouping. For example, you can group by an attribute part:

select((s.dob.year, avg(s.gpa)) for s in Student)

The birth year in this case is not a distinct attribute – it is a part of the dob attribute.

You can have expressions inside the aggregate functions:

select((item.order, sum(item.price * item.quantity))
        for item in OrderItem if item.order.id == 123)

Here is another way of making the same query:

select((order, sum(order.items.price * order.items.quantity))
        for order in Order if order.id == 123)

In the second case, we use the attribute lifting concept. The expression order.items.price creates an array of prices, while order.items.quantity generates an array of quantities. As the result, in this example, we’ll have the sum of quantity multiplied by the price for each order item.