• Login

DataSoft Corporation

HAVING

Use a HAVING clause in conjunction with a GROUP BY clause within SELECT statements to limit your view to groups whose aggregate values meet specific criteria.

The expressions in a HAVING clause may contain constants, set functions or an exact replica of one of the expressions in the GROUP BY expression list.

The Pervasive PSQL database engine does not support HAVING without GROUP BY.

The use of aliases is allowed within the HAVING clause. The alias must differ from any column names within the table.

Examples

The following example returns department names where the count of course names is greater than 5.

SELECT Dept_Name, COUNT(*) FROM Course GROUP BY Dept_Name HAVING COUNT(*) > 5

This same example could use aliases, in this case “dn” and “ct,” to produce the same result:

SELECT Dept_Name dn, COUNT(*) ct FROM Course GROUP BY dn HAVING ct > 5

Note that COUNT(expression) counts all non-NULL values for an expression across a predicate. COUNT(*) counts all values, including NULL values.

 

The next example returns department name that matches Accounting and has a number of courses greater than 5.

SELECT Dept_Name, COUNT(*) FROM Course GROUP BY Dept_Name HAVING COUNT(*) > 5 AND Dept_Name = 'Accounting'
Thursday, 18 April 2019 Posted in Pervasive SQL Command