- Article
Applies to: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
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
WHEREclause is applied first to the individual rows in the tables or table-valued objects in the Diagram pane. Only the rows that meet the conditions in theWHEREclause are grouped.The
HAVINGclause is then applied to the rows in the result set. Only the groups that meet theHAVINGconditions appear in the query output. You can apply aHAVINGclause only to columns that also appear in the GROUP BY clause or in an aggregate function.
Specify a WHERE and HAVING clause on two joined tables
Note
The database used in this article is the pubs database, available from Northwind and pubs sample databases for Microsoft SQL Server on GitHub.
For example, imagine that you're 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's over $10.00.
You can establish the first condition by including a WHERE clause, which discards any publishers that aren't 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_idWHERE publishers.state = 'CA'GROUP BY titles.pub_idHAVING AVG(price) > 10;In the Visual Database Tools of SQL Server Management Studio, you can create both HAVING and WHERE clauses in the Criteria pane. 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 Criteria pane, then specify one instance as part of the HAVING clause and the other instance as part of the WHERE clause.
Specify a WHERE condition in an aggregate query
Specify the groups for your query. For details, see Group Rows in Query Results (Visual Database Tools).
If it's not already in the Criteria pane, add the column on which you want to base the
WHEREcondition.Clear the Output column unless the data column is part of the GROUP BY clause or included in an aggregate function.
In the Filter column, specify the
WHEREcondition. The Query and View Designer adds the condition to theHAVINGclause of the SQL statement.Note
The query shown in the example for this procedure joins two tables,
titlesandpublishers.At this point in the query, the SQL statement contains a
HAVINGclause:SELECT titles.pub_id, AVG(titles.price)FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_idGROUP BY titles.pub_idHAVING publishers.state = 'CA'In the Group By column, select Where from the list of group and summary options. The Query and View Designer removes the condition from the
HAVINGclause in the SQL statement and adds it to theWHEREclause.The SQL statement changes to include a
WHEREclause instead:SELECT titles.pub_id, AVG(titles.price)FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_idWHERE publishers.state = 'CA'GROUP BY titles.pub_id;
