Use HAVING and WHERE clauses in the same query (ADP)

In some instances, you might want to exclude individual rows from groups (using a WHERE clause) before applying a condition to groups as a whole (using a HAVING clause).

A HAVING clause is like a WHERE clause, but applies only to groups as a whole (that is, to the rows in the result set representing groups), whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. In that case:

• The WHERE clause is applied first to the individual rows in the tables, views, or functions in the diagram pane. Only the rows that meet the conditions in the WHERE clause are grouped.
• The HAVING clause is then applied to the rows in the result set that are produced by grouping. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function.

For example, imagine that you are joining the titles and publishers tables to create a query showing the average book price for a set of publishers. You want to see the average price for only a specific set of publishers— perhaps only the publishers in the state of California. And even then, you want to see the average price only if it is over $10.00.

You can establish the first condition by including a WHERE clause, which discards any publishers that are not in California, before calculating average prices. The second condition requires a HAVING clause, because the condition is based on the results of grouping and summarizing the data. The resulting SQL statement might look like this:

SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
 ON titles.pub_id = publishers.pub_id
WHERE publishers.state = 'CA'
GROUP BY titles.pub_id
HAVING AVG(price) > 10
				

You can create both HAVING and WHERE clauses in the Grid pane of the Query Designer. By default, if you specify a search condition for a column, the condition becomes part of the HAVING clause. However, you can change the condition to be a WHERE clause.

You can create a WHERE clause and HAVING clause involving the same column. To do so, you must add the column twice to the Grid pane, then specify one instance as part of the HAVING clause and the other instance as part of the WHERE clause.

1. In the Database window, click Queries under Objects, click the query you want to open, and then click Design on the database window toolbar.
2. Specify the groups for your query.

   How?

   Background information

   If you want to create subtotals or show other summary information for subsets of a table, you create groups using an aggregate query. Each group summarizes the data for all the rows in the table that have the same value.

   For example, you might want to see the average price of a book in the titles table, but break the results down by publisher. To do so, you group the query by publisher (for example, pub_id). The resulting query output might look like this:

   

   When you group data, you can display only summary or grouped data, such as:

   • The values of the grouped columns (those that appear in the GROUP BY clause). In the example above, pub_id is the grouped column.
   • Values produced by aggregate functions such as SUM( ) and AVG( ). In the example above, the second column is produced by using the AVG( ) function with the price column.

   You cannot display values from individual rows. For example, if you group only by publisher, you cannot also display individual titles in the query. Therefore, if you add columns to the query output, the Query Designer automatically adds them to the GROUP BY clause of the statement in the SQL pane. If you want a column to be aggregated instead, you can specify an aggregate function for that column.

   If you group by more than one column, each group in the query shows the aggregate values for all grouping columns.

   For example, the following query against the titles table groups by publisher (pub_id) and also by book type (type). The query results are ordered by publisher and show summary information for each different type of book that the publisher produces:

   SELECT pub_id, type, SUM(price) Total_price
   FROM titles
   GROUP BY pub_id, type
   										

   The resulting output might look like this:

   

   Group rows

   1. In the Database window, click Queries under Objects, click the query you want to open, and then click Design on the database window toolbar.
   2. Start the query by adding the tables, views, or functions you want to summarize to the Diagram pane.

      How?

      When you create a query, you are retrieving data from a table, view, or function. To work with any of these objects in your query, you add them to the Diagram pane.

      Add a table, view, or user-defined function to the query

      1. In the Database window, click Queries under Objects, click the query you want to open, and then click Design on the database window toolbar.
      2. In the Diagram pane, right-click the background and choose Add Table from the shortcut menu.
      3. In the Add Table dialog box, select the Tables, Views, or Functions tab.
      4. In the list of items, double-click each item you want to add.
      5. When you finish adding items, click Close.

      The Query Designer updates the Diagram pane, Grid pane, and SQL pane accordingly.

      Alternatively, you can drag objects onto the Diagram pane. You can drag a table, view, or inline function from the database window.

      You can also drag columns or tables from the Database Designer or paste them from the Clipboard.

      Tables and views are automatically added to the query when you reference them in the statement in the SQL pane.

      The Query Designer will not display data columns for an table, view, or inline function if you do not have sufficient access rights. In such cases, only a title bar and the * (All Columns) check box are displayed for the table, view, or inline function.

      Add an existing query to a new query

      1. If necessary, click SQL to show the SQL pane.
      2. In the SQL pane, type a right and left parentheses () after the word FROM.
      3. Open the Query designer for the existing query . (You now have two Query Designers open.)
      4. Display the SQL pane for the inner query – the existing query you are including in the new, outer query.
      5. Select all the text in the SQL pane, and copy it to the Clipboard.
      6. Click in the SQL pane of the new query, situate the cursor between the parentheses you added, and paste the contents of the Clipboard.
      7. Still in the SQL pane, add an alias after the right parenthesis. For more information on SQL aliases and subqueries, see the Microsoft SQL Server documentation.
   3. Right-click the background of the Diagram pane, then choose Group By from the shortcut menu. The Query Designer adds a Group By column to the grid in the Grid pane.
   4. Add the column or columns you want to group to the Grid pane. If you want the column to appear in the query output, be sure that the Output column is selected for output.

      The Query Designer adds a GROUP BY clause to the statement in the SQL pane. For example, the SQL statement might look like this:

      SELECT pub_id
      FROM titles
      GROUP BY pub_id
      												

   5. Add the column or columns you want to aggregate to the Grid pane. Be sure that the column is marked for output.
   6. In the Group By grid cell for the column that is going to be aggregated, select the appropriate aggregate function.

      The Query Designer automatically assigns a column alias to the column you are summarizing. You can replace this automatically generated alias with a more meaningful one.

      

      The corresponding statement in the SQL pane might look like this:

      SELECT pub_id, SUM(price) AS Totalprice
      FROM titles
      GROUP BY pub_id
      												

3. If it is not already in the Grid pane, add the column on which you want to base the WHERE condition.
4. Clear the Output column unless the data column is part of the GROUP BY clause or included in an aggregate function.
5. In the Criteria column, specify the WHERE condition. The Query Designer adds the condition to the HAVING clause of the SQL statement.

   Note   The query shown in the example for this procedure joins two tables, titles and publishers.

   At this point in the query, the SQL statement contains a HAVING clause:

   SELECT titles.pub_id, AVG(titles.price)
   FROM titles INNER JOIN publishers 
    ON titles.pub_id = publishers.pub_id
   GROUP BY titles.pub_id
   HAVING publishers.state = 'CA'
   						

6. In the Group By column, select Where from the list of group and summary options. The Query Designer removes the condition from the HAVING clause in the SQL statement and adds it to the WHERE clause.

   The SQL statement changes to include a WHERE clause instead:

   SELECT titles.pub_id, AVG(titles.price)
   FROM titles INNER JOIN publishers 
    ON titles.pub_id = publishers.pub_id
   WHERE publishers.state = 'CA'
   GROUP BY titles.pub_id