Previous Topic Next topic Print topic


GROUP BY HAVING

Apply column functions to data elements that are collected into groups and define a hierarchy for these groups.
Restriction: This topic applies only when the AppMaster Builder AddPack has been installed, and applies only to Windows platforms.

Use the HAVING clause, which acts as a WHERE clause, with GROUP BY to identify or evaluate the groups you want to include. A HAVING clause:

  • Names a grouping column or column function with its search conditions or qualifications
  • Can link qualifications
  • Can test the number of rows found for a group

Target:

SQL

Comments:

  • Code GROUP BY with DB-DECLARE and DB-PROCESS only.
  • Use column names or expressions.
  • Each column name in the SELECT list must be in the GROUP BY statement, and vice versa.
  • Use the ORDER BY keyword with GROUP BY to sort rows.
  • Separate each item in the GROUP BY and ORDER BY clauses with a space; commas are optional.
  • The HAVING clause should directly follow the GROUP BY statement.
  • To link qualifications, use the Boolean operators AND and OR in the HAVING clause
  • To test the number of rows found for a group, code the HAVING clause with COUNT--COUNT(*)operator value

Examples:

This statement:
DB-PROCESS REC EMPLOYEE-REC
... DB-PROCESS-ID D2EMP
... EMP-DEPT
... MAX(EMP-SALARY)
... WHERE EMP-NAME NOT NULL
... GROUP BY EMP-DEPT
... ORDER BY EMP-DEPT
Yields this result:
EMP-DEPT    EMP-SALARY
--------    ----------
FIN001      66700.000
FIN201      45250.000
MKT001      72300.000
PAY001      68800.000
PAY002      43500.000
SYS001      75000.000
This statement first groups rows by EMP-DEPT number, then within department by EMP-SEX, and then calculates maximum rates and salaries for each group.
DB-PROCESS REC EMPLOYEE-REC
... DB-PROCESS-ID D2EMP
... EMP-DEPT
... EMP-SEX
... MAX(EMP-RATE)
... MAX(EMP-SALARY)
... WHERE EMP-NAME NOT NULL
... GROUP BY EMP-DEPT,EMP-SEX
... HAVING COUNT(*) > 2
... AND MIN(EMP-RATE) > 3
... ORDER BY EMP-DEPT,EMP-SEX
Resulting in:
EMP-DEPT    EMP-SEX     EMP-RATE    EMP-SALARY
---------    -------    ---------   ----------
FIN001      F           32.067      66700.000
FIN001      M           31.105      64700.000
FIN201      F           19.711      41000.000
FIN201      M           21.754      45250.000
PAY001      F           33.076      68800.000
PAY001      M           31.884      66320.000
SYS001      F           32.692      68000.000
SYS001      M           36.058      75000.000
This statement uses an expression rather than a column name:
DB-PROCESS 
... REC EMSAVING-REC DB-PROCESS-ID ID-DETAIL  
... RIGHT((ADDRESS_1,12),WS-ADDRESS1) AS ADDRESS-RIGHT1  
... PREVIOUS_BALANCE + INTEREST_PAID                    
... SUM(INTEREST_PAID)
... WHERE STATE = 'MD'
... GROUP BY      
... RIGHT(ADDRESS_1,12)
... PREVIOUS_BALANCE + INTEREST_PAID
... SUM(INTEREST_PAID)                   
... ORDER BY      
... PREVIOUS_BALANCE + INTEREST_PAID
... ADDRESS_1
Which produces this generated code:
EXEC SQL DECLARE ID_DETAIL CURSOR
  FOR SELECT
  RIGHT(  ADDRESS_1,12)
      AS ADDRESS-RIGHT1,
  PREVIOUS_BALANCE + INTEREST_PAID,
  SUM( INTEREST_PAID)
  FROM
  MFIFAT.EMSAVINGS
  WHERE
  STATE = 'MD'
  GROUP BY
  RIGHT(ADDRESS_1,12),
  PREVIOUS_BALANCE
  +
  INTEREST_PAID,
  SUM(INTEREST_PAID)
  ORDER BY
  PREVIOUS_BALANCE
  +
  INTEREST_PAID,
  ADDRESS_1
END-EXEC
perform OPEN CURSOR paragraph,
FETCH CURSOR paragraph, and CLOSE CURSOR paragraph
Previous Topic Next topic Print topic