Conditional count

There is another way of using the count() function. Let’s assume that we want to get three numbers for each group:

• The number of students that have a GPA less than 3

• The number of students with GPA between 3 to 4

• The number of students with GPA higher than 4

The query can be constructed this way:

select((g, count(s for s in g.students if s.gpa <= 3),
           count(s for s in g.students if s.gpa > 3 and s.gpa <= 4),
           count(s for s in g.students if s.gpa > 4)) for g in Group)

Although this query will work, it is pretty long and not very effecive - each count will be translated into a separate subquery. For such situations, Pony provides a “conditional COUNT” syntax:

select((s.group, count(s.gpa <= 3),
                 count(s.gpa > 3 and s.gpa <= 4),
                 count(s.gpa > 4)) for s in Student)

This way, we put our condition into the count() function. This query will not have subqueries, which makes it more effective.

Note

The queries above are not entirely equivalent: if a group doesn’t have any students, then the first query will select that group having zeros as the result of count(), while the second query simply will not select the group at all. This happens because the second query selects the rows from the table Student, and if the group doesn’t have any students, then the table Student will not have any rows for this group.

If you want to get rows with zeros, then an effective SQL query should use the left_join() function:

left_join((g, count(s.gpa <= 3),
       count(s.gpa > 3 and s.gpa <= 4),
       count(s.gpa > 4)) for g in Group for s in g.students)