Aggregate functions

Aggregate functions perform a calculation on a set of rows and return a single row. PostgreSQL provides all the standard SQL aggregate functions:

• AVG(): This function returns the average value.
• COUNT(): This function returns the number of values.
• MAX(): This function returns the maximum value.
• MIN(): This function returns the minimum value.
• SUM(): This function returns the sum of values.

Aggregate functions are used in conjunction with the group by clause. A group by clause splits a resultset into groups of rows and aggregate functions perform calculations on them. For example, if we wanted to count how many records there are for each category, PostgreSQL first groups the data and then counts it. The following diagram illustrates the process:

Figure 5.6: Group by aggregation

This diagram illustrates that PostgreSQL, before grouping the data, sorts it internally. Therefore, we must remember that a grouping...