Aggregation

You can use the following five aggregate functions for declarative queries: sum(), count(), min(), max(), avg() and group_concat(). Let’s see some examples of simple queries using these functions.

Total GPA of students from group 101:

sum(s.gpa for s in Student if s.group.number == 101)

Number of students with a GPA above three:

count(s for s in Student if s.gpa > 3)

First name of a student, who studies philosophy, sorted alphabetically:

min(s.name for s in Student if "Philosophy" in s.courses.name)

Birth date of the youngest student in group 101:

max(s.dob for s in Student if s.group.number == 101)

Average GPA in department 44:

avg(s.gpa for s in Student if s.group.dept.number == 44)

Names of students of group 101 joined by comma:

group_concat(s.name for s in Student if s.group.number == 101)

Note

Although Python already has the standard functions sum(), count(), min(), and max(), Pony adds its own functions under the same names. Also, Pony adds its own avg() and group_concat() functions. These functions are implemented in the pony.orm module and they can be imported from there either “by the star”, or by its name.

The functions implemented in Pony expand the behavior of standard functions in Python; thus, if in a program these functions are used in their standard way, the import will not affect their behavior. But it also allows specifying a declarative query inside the function.

If one forgets to import these functions from the pony.orm package, then an error will appear upon use of the Python standard functions sum(), count(), min(), and max() with a declarative query as a parameter:

TypeError: Use a declarative query in order to iterate over entity

Aggregate functions can also be used inside a query. For example, if you need to find not only the birth date of the youngest student in the group, but also the student himself, you can write the following query:

select(s for s in Student if s.group.number == 101
           and s.dob == max(s.dob for s in Student
                                        if s.group.number == 101))

Or, for example, to get all groups with an average GPA above 4.5:

select(g for g in Group if avg(s.gpa for s in g.students) > 4.5)

This query can be shorter if we use Pony attribute lifting feature:

select(g for g in Group if avg(g.students.gpa) > 4.5)

And this query shows all tags for article

select((article, group_concat(article.tags)) for article in Aricle)