| **Navigation:**  SQL Driver Properties(General) >====== PROP:GroupBy, PROP:Having ====== | [[prop drivertracing.htm|{{btn_prev_n.gif|Previous page}}]][[coming future.htm|{{btn_home_n.gif|Return to chapter overview}}]][[prop hdbc.htm|{{btn_next_n.gif|Next page}}]] |
|  ||

The SQL view engine supports PROP:GroupBy and PROP:Having. These properties allow you to add respectively GROUP BY and HAVING clauses to your SELECT statement. Note that PROP:GroupBy must be set first to allow PROP:Having to be generated.

**Example:**

**  PROGRAM**

**  MAP**

**  END**

**EMP       FILE,DRIVER('ORACLE'),NAME('EMP'),PRE(EMP)**

**P_EKY_EMP  KEY(EMP:EMPNO),NOCASE,OPT,PRIMARY**

**KEY_DEP    KEY(EMP:DEPTNO),DUP,NOCASE,OPT**

**Record     RECORD**

**EMPNO       SHORT         !Emp-no**

**ENAME       CSTRING(11)   !Employee name**

**JOB         CSTRING(10)   !Job**

**HIREDATE    DATE          !Hiredate**

**MGR         SHORT         !Manager**

**SAL         PDECIMAL(7,2) !Salary**

**COMM        PDECIMAL(7,2) !Commisison**

**DEPTNO      BYTE**

**           END**

**          END**

**MyView VIEW(EMP)**

**    PROJECT(EMP:Mgr)**

**    PROJECT(EMP:Sal)**

**       END**

**  CODE**

**   OPEN(EMP)**

**   OPEN(MyView)**

**   MyView{'EMP:Sal',PROP:Name} = 'sum(sal)'**

**   MyView{****PROP:GroupBy****} = 'Mgr'**

**   MyView{****PROP:Having****} = 'sum(sal) > 100000'**

**   SET(MyView)**

**   NEXT(MyView)**

The example code above is the equivalent to "SELECT mgr, sum(sal) FROM EMP GROUP BY mgr HAVING sum(sal) > 100000"

**In other words, this code will return a list of all Manager IDs and the total salary of their subordinates if their subordinates make a total of more than 100000.**