AntDB-Oracle Compatibility Developer’s Manual P3–22

WHERE clause

The WHERE clause is optional and is used as follows

WHERE condition

condition is an expression whose result is a Boolean type. No record will appear in the output that does not satisfy this condition. If the expression returns "true" when the value in the actual record is referenced by any variable, then the record satisfies the query condition.

GROUP BY clause

The usage method of optional GROUP BY clause is as follows

GROUP BY expression [, ...]

The GROUP BY clause streamlines all output rows that share the value of a grouping expression into a single row. The grouping expression can be the name of an input column, or the name or ordinal number of an output column, or any expression formed from the input column values. In case of semantic ambiguity, the name appearing after the GROUP BY clause is interpreted as the name of the input column rather than the name of the output column.

If an aggregate function is used, then the function operates on all records in each group. A separate value is generated for each group. (In contrast, if there is no GROUP BY clause, the aggregation function produces a single value on all the queried rows.) When the GROUP BY clause appears, the aggregation function cannot be used on columns that are not used for grouping in the SELECT list, because there are multiple possible values to return for the non-grouped columns.

Example:

select job,sum(SAL) from jobhist group by job;

      JOB    | SUM(SAL)  
-------------+--------------
 CLERK       |     7140
 PRESIDENT   |     5000
 MANAGER     |     8275
 SALESMAN    |     5600
 ANALYST     |     6000
(5 rows)

HAVING clause

The HAVING clause is not mandatory, it is used as follows

HAVING condition

where condition is the same as specified in the WHERE clause.

The HAVING clause is used to eliminate rows from the grouped rows that do not satisfy the condition. HAVING differs from the WHERE clause; the WHERE clause filters out specific rows before applying the GROUP BY clause, while the HAVING clause filters out the grouped rows created by the GROUP BY clause. Unless referenced in an aggregate function, each column referenced in a condition must explicitly reference a grouping column.