HAVING Clause (SQL Server Compact)

Specifies a search condition for a group or an aggregate. HAVING is usually used with the GROUP BY clause.

Syntax

[ HAVING < search_condition > ] 

Arguments

  • < search_condition >
    Specifies the search condition for the group to meet. The search condition can use aggregate and nonaggregate expressions. The only columns that can be used in the nonaggregate expressions are those specified as grouping columns in the GROUP BY clause. This is because the group-by columns represent common properties for the entire group. Likewise, the aggregate expressions represent a common property for the entire group. The HAVING clause search condition is expressing a predicate over the properties of the group.

    The image and ntext data types cannot be used in a HAVING clause.

Remarks

The following queries are allowed in SQL Server, but are not allowed in SQL Server Compact, and cause the following error message to appear: "In aggregate and grouping expressions, the HAVING clause can contain only aggregate functions and grouping expressions."

select count(*), c1+c2 from s1 group by c1+c2 having c1+c2 <10; 
select count(*), x from (select *, col1 + col2 as x from oj1) d group by x having x <4;

However, these queries are allowed in SQL Server Compact if a simple column variable is used instead of an expression, such as (c1+c2).

Example

The following example returns a list of all products with a Total Count of > 50.

SELECT [Product ID], COUNT(*) AS Total FROM [Order Details] GROUP BY [Product ID] HAVING (COUNT(*) > 50)