Specifying Result Tables › Summarizing Group Values

Summarizing Group Values

Use the GROUP BY clause to apply a function to each group of column values. Except for the group column(s), any other column you specify can be the argument of a column function.

 Problem

Show the state, maximum year-to-date sales, minimum year-to-date
sales, and average year-to-date sales for each state in the CUSTOMERS
table.  Each group must have more than one row and the maximum
year-to-date sales must be more than $200,000.
 Solution

               .
               .
       (COBOL statements)
               .
               .
  1   EXEC SQL
  2       DECLARE YTDLIST CURSOR FOR
  3           SELECT STATE, MAX(YTD_SALES), MIN(YTD_SALES), AVG(YTD_SALES)
  4           FROM CUSTOMERS
  5           GROUP BY STATE
  6           HAVING COUNT(*) > 1 AND MAX(YTD_SALES) > 200000
  7   END-EXEC.
               .
               .
       (COBOL statements)
               .
               .

Line 3

The SELECT statement finds the maximum and minimum year-to-date sales and calculates the average year-to-date sales.

Line 5

The GROUP BY clause specifies that each function in the SELECT statement is to be applied to each group of a STATE value and one row is to be returned for each distinct state.

Line 6

The HAVING clause limits the result table to only those groups where the STATE value was found more than once and the maximum year-to-date sales for the state was greater than $200,000.