Please enable JavaScript to view this site.

R:BASE 11 Help

Navigation: Command Index > W > WHERE

HAVING

Scroll Prev Top Next More

This clause determines which rows of data to include based on the results of a prior GROUP BY clause.

 

WHERE_HAVING

 

Options

 

AND

OR

AND indicates two conditions must both be true.

OR indicates either condition must be true.

 

condition

Specifies a combination of one or more expressions and/or operations that would evaluate to either true or false. See the "HAVING Conditions" below.

 

NOT

Reverses the meaning of an operator or indicates that a condition is not true.

 

About the HAVING command

 

The optional HAVING clause selects rows that meet one or more conditions from among the results of the GROUP BY clause. HAVING works the same as a WHERE clause with the following exceptions:

 

A WHERE clause modifies the intermediate results of a FROM clause; a HAVING clause modifies the intermediate results of a GROUP BY clause.

A HAVING clause can include SELECT functions.

 

HAVING Conditions:

 

havingcond

 

Examples

 

To display sales information for only those employees who have made more than one sale to the same customer, add a HAVING clause such as the following to one of the examples shown previously in GROUP BY. When used in a HAVING clause, SELECT functions compute results based on the values grouped in the specified column. In this HAVING clause, COUNT returns the number of values grouped in the transid column.

 

SELECT empid, custid FROM transmaster +

WHERE netamount < $100,000 +

GROUP BY empid, custid HAVING COUNT(transid) > 1

 

Intermediate Result Table-HAVING COUNT(transid) > 1

 

transid                        custid        empid        netamount        

4975, 4980, 5000, 5060        101        102        $87,500, $22,500, $40,500, $57,500                        

4790, 5050, 5070                104        129        $6,400, $56,250, $95,500                        

5080                                100        133        $32,400, $88,000