Although you can still use WHERE clauses to limit the result set based on values that exist in the columns specified in the FROM clause, the HAVING clause allows you to filter based on the results of the calculations performed by the aggregate functions.
The following query uses a HAVING clause that returns only product subcategories where the minimum price is greater than $200:
SELECT Production.ProductCategory.ProductCategoryID , Production.Product.ProductSubcategoryID , AVG(Listprice) AS 'Average'
, MIN(Listprice) AS 'Minimum' , MAX(Listprice) AS 'Maximum'
FROM Production.Product JOIN Production.ProductSubcategory ON Production.ProductSubcategory.ProductSubcategoryID = Production.Product.ProductSubcategoryID JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID WHERE ListPrice <> 0
GROUP BY Production.ProductCategory.ProductCategoryID, Product.ProductSubcategoryID WITH ROLLUP
HAVING MIN(ListPrice) > 200;
The result set for this query is shown in Figure 1-11.
FiGURe 1-11 Results with the HAVING clause added
Practice
implementing aggregate Queries
In this practice session, you create aggregate queries that progress from returning the results of a system aggregate function to returning aggregates grouped by a variety of columns and including GROUPING SETS.
exercise Use the GROUP BY Statement
In this exercise, you use aggregate functions and the GROUP BY clause with a variety of operators, such as ROLLUP and HAVING, that provide summary and detail information in the result set.
1. Open a new query window in SSMS.
2. In the existing query window, type and execute the following code to return the grand total of all products ordered on all lines of all sales orders:
USE AdventureWorks2008;
3. In the existing query window, below the existing code, type, highlight, and execute the following code to return the total list price for each combination of sales order IDs and product IDs. Then, review the result set.
SELECT SalesOrderID, ProductID, SUM(Linetotal) AS 'Total' FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID, ProductID ORDER BY SalesOrderID;
4. In the existing query window, below the existing code, type, highlight, and execute the following code to provide a grand total, as well as a subtotal of all products on each sales order. Then, review the result set:
SELECT SalesOrderID, ProductID, SUM(Linetotal) AS 'Total' FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID, ProductID WITH ROLLUP
ORDER BY SalesOrderID;
5. In the existing query window, below the existing code, type, highlight, and execute the following code to reverse the order of the columns listed in the GROUP BY clause and provide the subtotal of each product across all sales orders and a grand total. The sort order has been changed to make the results easier to review. After executing the query, review the result set:
SELECT SalesOrderID, ProductID, SUM(Linetotal) AS 'Total' FROM Sales.SalesOrderDetail
GROUP BY ProductID, SalesOrderID WITH ROLLUP
ORDER BY ProductID;
6. In the existing query window, below the existing code, type, highlight, and execute the following code to provide subtotal information on both the sales order IDs and the product IDs. Review the result set:
SELECT SalesOrderID, ProductID, SUM(Linetotal) AS 'Total' FROM Sales.SalesOrderDetail
GROUP BY ProductID, SalesOrderID WITH CUBE
ORDER BY SalesOrderID, ProductID;
7. In the existing query window, below the existing code, type, highlight, and execute the following code to limit the result set to line totals that exceed 10,000. Review the results:
SELECT SalesOrderID, ProductID, SUM(Linetotal) AS 'Total' FROM Sales.SalesOrderDetail
WITH CUBE
HAVING SUM(Linetotal) > 10000 ORDER BY SalesOrderID, ProductID;
8. In the existing query window, below the existing code, type, highlight, and execute the following code to create a grouping set on the SalesOrderID and ProductID columns without any rollup operations. Review the result set:
SELECT SalesOrderID, ProductID, SUM(Linetotal) AS 'Total' FROM Sales.SalesOrderDetail
GROUP BY GROUPING SETS (ProductID, SalesOrderID ) ORDER BY SalesOrderID, ProductID
9. Save the script and close the query window, but leave SSMS open for the next practice.
Lesson Summary
n Aggregate functions perform calculations on expressions that are provided as input to the function.
n Use the GROUP BY clause when aggregates should be applied based on the data in specific rows rather than the entire table.
n Include all columns listed in a SELECT, WHERE, or ORDER BY clause in the GROUP BY clause.
n Use ROLLUP and CUBE to provide additional summary information.
n Use the GROUPING function to show which rows hold summary data provided by the
ROLLUP or CUBE operators.
n Use GROUPING SETS to provide greater flexibility and readability to your GROUP BY queries.
Lesson 4: combining Datasets
SQL Server 2008 provides several operators that provide you with the ability to combine or compare the results from multiple SELECT statements. The UNION operator has been available from the first version of SQL Server to provide the ability to combine the result sets from multiple queries. On the other hand, the EXCEPT and INTERSECT operators were introduced in SQL Server 2005 to provide the ability to compare the results from two queries and provide a new result set based on whether or not there are rows in common between the result sets.
In addition, datasets can be manipulated by using the APPLY operator to apply a table-valued function against each row of the query results from what is defined as the outer table.
note USeR-DeFineD FUnctiOnS
SQL Server 2008 provides you with the ability to create user-defined functions (UDFs). Scalar functions return a single value of a specified data type, while inline table-valued functions and multi-statement table-valued functions return a table data type. For more information, see Chapter 5, “Programming Microsoft SQL Server with T-SQL User-Defined Stored Procedures, Functions, Triggers, and Views.”
After this lesson, you will be able to:
n Write queries that use the UNION operator to combine result sets.
n Write queries that use the EXCEPT and INTERSECT operators to compare the results from multiple queries.
Estimated lesson time: 30 minutes
The UNION, EXCEPT, and INTERSECT operators can be specified between two or more queries to provide a single result set. While the UNION operator combines the result sets from the multiple queries into a single result set, the EXCEPT and INTERSECT operators compare the result sets of two queries to determine what subset of rows should be included in the final result set.