Having clauses define selection criteria for aggregate rows. (Aggregate rows are derived from aggregate functions such as AVG, COUNT, MIN, MAX, and SUM. Aggregate functions summarize the results of a query rather than listing all of the rows.)
For example, assume you want to create a query that retrieves a list of products and their prices, and that you want to group the products by category (for example, household products and commercial products). Next, assume that you want to determine the most expensive product in each category. Finally, assume that you only want the product to appear in your report if the price is greater than $1,000.
To do this, you would create an expression with an aggregate function such as MAX(Price) to determine the most expensive item ordered. You would then group the data by Category ID. Finally, you would create a Having clause such as MAX(Price)>1000 to limit the items printed to items over $1,000.
Think of Having clauses as Where clauses for grouped columns. Unlike Where clauses, however, you can include aggregate expressions in a Having clause.
To add a Having clause to a query:
Go to Query Builder - Fields (see Selecting Fields).
Click New, choose Expression, and create an expression that uses an aggregate function.
For example, MAX(Products.Price) calculates the maximum price in the Products table.
Click Group By and define how to group the data.
Using the example discussed above, you could group the data by Category ID.
This step is optional. When you create an expression that uses an aggregate function, SQR Production Reporting Studio automatically groups the data for you.
Click Having.
SQR Production Reporting Studio enables Having only when a Group By statement exists. Group By statements exist when you create an expression that uses an aggregate function and SQR Production Reporting Studio generates the Group By statement automatically, or when you define the Group By statement manually. If neither of these conditions exist, Having is disabled.
After you click Having, the Having Clauses dialog box is displayed
You can combine several Having clauses with the logical operators AND, OR, or ELSE. If you create more than one Having clause, select the desired operator under Logical Operator. To change the order in which the Having clauses appear, click the desired Having clause and click Move Up or Move Down.
Click Add Clause and enter information about the Having clause in the dialog boxes that appear, or click Add Custom and enter a custom Having Clause in the Having Clause Builder.
As discussed previously, Having clauses are essentially Where clauses for grouped columns. As a result, the dialog boxes used to create a Having clause are the same as the dialog boxes used to create a Where clause. See Adding Conditions to the Data (Creating Where Clauses) for detailed information on how to create Where and Having clauses.