Aggregate Functions

1. In SQL we can compute functions on groups of tuples using the group by clause.

   Attributes given are used to form groups with the same values. SQL can then compute

   • average value - avg
   • minimum value - min

   • maximum value - max

   • total sum of values - sum
   • number in group - count

   These are called aggregate functions. They return a single value.

2. Some examples:
   1. Find the average account balance at each branch.

      

   2. Find the number of depositors at each branch.

      

      We use distinct so that a person having more than one account will not be counted more than once.

   3. Find branches and their average balances where the average balance is more than $1200.

      

      Predicates in the having clause are applied after the formation of groups.

   4. Find branches with the highest average balance. We cannot compose aggregate functions in SQL, e.g. we cannot do max (avg ...)).

      Instead, we find the branches for which average balance is greater than or equal to all average balances:

      

   5. Find the average balance of all depositors living in Vancouver who have at least three accounts:

      

      If a where clause and a having clause appear in the same query, the where clause predicate is applied first.

      • Tuples satisfying where clause are placed into groups by the group by clause.
      • The having clause is applied to each group.

      • Groups satisfying the having clause are used by the select clause to generate the result tuples.
      • If no having clause is present, the tuples satisfying the where clause are treated as a single group.