• Article
  • 3 minutes to read

Applies to: yesSQL Server (all supported versions)

In any instances, you power lack to exclude individual rows from groups (using a WHERE clause) before applying a condition to groups American Samoa a completely (victimisation a HAVING clause).

A HAVING clause is like a WHERE article, simply 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 commode contain both a WHERE clause and a HAVING clause. Therein case:

  • The WHERE clause is applied first to the unshared rows in the tables or table-valued objects in the Diagram pane. Sole the rows that meet the conditions in the WHERE clause are grouped.

  • The HAVING clause is and then applied to the rows in the result set. Solitary the groups that meet the HAVING conditions come out in the enquiry output. You can go for a HAVING clause only to columns that also appear in the Grouping BY clause or in an aggregate purpose.

For instance, imagine that you are connection the titles and publishers tables to create a question showing the modal book Leontyne Price for a set of publishers. You want to see the mean damage for single a specific set of publishers - perhaps only the publishers in the state of CA. And even so, you want to see the average price only when it is o'er $10.00.

You can establish the first condition by including a WHERE clause, which discards any publishers that are not in California, earlier shrewd average prices. The second condition requires a HAVING article, because the condition is supported the results of group and summarizing the data. The resulting SQL financial statement might face like this:

            Choose 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 some HAVING and WHERE clauses in the Criteria pane. By default, if you destine 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 stool create a WHERE clause and HAVING clause involving the same editorial. To do so, you must add the column twice to the Criteria paneling, then specify one instance as part of the HAVING clause and the other instance as part of the WHERE clause.

To specify a WHERE train in an aggregate query

  1. Specify the groups for your query. For details, see Group Rows in Query Results.

  2. If it is not already in the Criteria pane, add the column on which you want to base the WHERE condition.

  3. Pass the Output column unless the data column is set forth of the GROUP BY clause or included in an aggregate function.

  4. In the Filter column, specify the WHERE condition. The Query and View Designer adds the condition to the HAVING clause of the SQL statement.

    Note

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

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

                    SELECT titles.pub_id, AVG(titles.price)   FROM titles INNER Link publishers       ON titles.pub_id = publishers.pub_id   GROUP Aside titles.pub_id   HAVING publishers.state = 'Calcium'

  5. In the Grouping By column, superior Where from the tilt of group and sum-up options. The Query and View Designer removes the condition from the HAVING article in the SQL statement and adds it to the WHERE clause.

    The SQL statement changes to let in a WHERE article instead:

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

See Also

Sort and Grouping Question Results
Summarize Query Results