Adding a GROUP BY Clause and a HAVING Clause :: SAS(R) Data Integration Studio 4.6: User's Guide

Problem

You want to group your results by a selected variable. Then, you want to subset the number of groups displayed in the results.

Solution

You can add a GROUP BY clause to group the results of your query. You can also add a HAVING clause that uses an aggregate expression to subset the groups returned by the GROUP BY clause that are displayed in the query results.

Perform the following tasks:

Tasks

Add a GROUP BY Clause to an SQL Query in the Diagram Tab

Perform the following steps to add a GROUP BY clause to the SQL query in the Diagram tab in the Designer window:

Click Create in the Navigate pane to access the Diagram tab and the SQL Clauses pane.
Double-click Group by in the SQL Clauses pane. The Group by object is added to the query flow in the Diagram tab. Then, click Group by in the Navigate pane to access the Group by tab.
Select the column that you want to use for grouping the query results from the Available columns field. Then, move the column to the Group by columns field. The following display depicts a sample SQL query grouped with a GROUP BY clause.

Sample SQL Query Grouped with a GROUP BY Clause

Note that the Group by column pane is set on the Group by tab, and the resulting SQL code is highlighted on the Code tab. The GROUP BY clause in the sample query groups the results of the query by the region of the United States.

Add a HAVING Clause to an SQL Query in the Diagram Tab

Perform the following steps to add a HAVING clause to the SQL query in the Diagram tab in the Designer window:

Click Create in the Navigate pane to access the Diagram tab and the SQL Clauses pane.
Double-click Having in the SQL Clauses pane. The Having object is added to the query flow on the Diagram tab.
Click Having in the Navigate pane to access the Having tab.
Click New on the Having tab to begin the first condition of the expression. An editable row appears in the table near the top of the tab.
Enter the appropriate operands and operator for the first condition.
Add the remaining conditions for the HAVING clause. You need to add one row for each condition.
The condition that is created for the sample query is depicted in the SQL code generated in this step in the SQL field, as shown in the following display.

Sample SQL Query Subsetted with a HAVING Clause

Note that the SQL code for the HAVING clause that is shown in the SQL field is identical to the highlighted HAVING clause code that is displayed on the Code tab. (To highlight the code for a query object, right-click the object in the Navigate pane and click Find In. Then, click Code in the submenu.) The HAVING clause subsets the groups that are included in the results for the query. In the sample, only the regions with an average population density of less than 100 are included in the query results.