Postgres – adding custom aggregates most()

22 05 2009

Postgres allows you to define custom aggregates. In most cases the stock aggregate functions (AVG, MIN, MAX, SUM etc.) will meet our needs.  But lets say we have a table of teachers joined to the classes they teach and we want to group by teacher and see what subject area the teacher teaches. So what results will you get if the teacher has 5 English classes and 1 Science class? If you use MAX() she’ll show up as a Science teacher. What we want is the subject area that shows up most often.

Enter the most() aggregate. We’ll use it to find the value that occurs most in our group. We need a few helper functions for this. Read the rest of this entry »