| **Navigation:**  SQL Drivers >====== VIEW support for aggregate functions ====== | [[runtime sql properties for views using sql drivers.htm|{{btn_prev_n.gif|Previous page}}]][[coming future.htm|{{btn_home_n.gif|Return to chapter overview}}]][[debugging your sql application.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 SQL clauses to your SELECT statement.

{{notebox.jpg|NoteBox.jpg}}

PROP:GroupBy must be set first to allow PROP:Having to be generated.

**See Also:** [[runtime sql properties for views using sql drivers.htm|Runtime SQL Properties for Views using SQL Drivers]]

**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.**