Function count

Aggregate queries often need to calculate the quantity of something. Here is how we get the number of students in Group 101:

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

The number of students in each group related to the department 44:

select((g, count(g.students)) for g in Group if g.dept.number == 44)

or this way:

select((s.group, count(s)) for s in Student if s.group.dept.number == 44)

In the first example the aggregate function count() receives a collection, and Pony will translate it into a subquery. (Actually, this subquery will be optimized by Pony and will be replaced with LEFT JOIN).

In the second example, the function count() receives a single object instead of a collection. In this case Pony will add a GROUP BY section to the SQL query and the grouping will be done on the s.group attribute.

If you use the count() function without arguments, this will be translated to SQL COUNT(*). If you specify an argument, it will be translated to COUNT(DISTINCT column).