In the past few Jump Start columns, I've covered the different aspects of using the SQL SELECT statement. The SELECT statement is the core of every database application, and understanding and mastering its use is essential to creating efficient and effective database applications.
Let's dive further into the SELECT statement by discovering how to use the HAVING clause. As with the WHERE the clause, the main purpose of the HAVING clause is to restrict the contents of the result set that's returned by the SELECT statement. The difference between the HAVING and the WHERE clauses is the fact that the WHERE keyword can't be used with aggregate functions such as SUM().
The following query illustrates how you can use the GROUP BY and HAVING statements to return the total number of units in stock from the Northwind Products table for all products that have a unit price greater than $10:
- USE northwind
- SELECT ProductID, ProductName,SUM(UnitsInStock) \[In Stock over $10\] FROM Products
- GROUP BY ProductID, ProductName
- HAVING SUM(UnitPrice) > 10