Structured Query Language 122022 Pierce College CIS 261

Structured Query Language 1/2/2022 Pierce College CIS 261 SQL 1

Module 7: GROUP BY and HAVING Aggregating data and filtering aggregated data. 1/2/2022 Pierce College CIS 261 SQL 2

GROUP BY “Groups a selected set of rows into a set of summary rows by the values of one or more columns or expressions in SQL Server 2012. One row is returned for each group. Aggregate functions in the SELECT clause <select> list provide information about each group instead of individual rows. ” “The HAVING clause is used with the GROUP BY clause to filter groups in the result set. ” http: //technet. microsoft. com/en-us/library/ms 177673. aspx 1/2/2022 Pierce College CIS 261 SQL 3

HAVING “Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. ” http: //technet. microsoft. com/en-us/library/ms 180199. aspx . 1/2/2022 Pierce College CIS 261 SQL 4

GROUP BY with JOINS “The following example retrieves the number of employees for each City from the Address table joined to the Employee. Address table. ” http: //technet. microsoft. com/en-us/library/ms 177673. aspx USE Adventure. Works 2012; GO SELECT a. City, COUNT(bea. Address. ID) Employee. Count FROM Person. Business. Entity. Address AS bea INNER JOIN Person. Address AS a ON bea. Address. ID = a. Address. ID GROUP BY a. City ORDER BY a. City; 1/2/2022 Pierce College CIS 261 SQL 5

GROUP BY with an expression Using a GROUP BY clause with an expression http: //technet. microsoft. com/en-us/library/ms 177673. aspx USE Adventure. Works 2012; GO SELECT DATEPART(yyyy, Order. Date) AS N'Year‘ , SUM(Total. Due) AS N'Total Order Amount‘ FROM Sales. Order. Header GROUP BY DATEPART(yyyy, Order. Date) ORDER BY DATEPART(yyyy, Order. Date); 1/2/2022 Pierce College CIS 261 SQL 6

GROUP BY with HAVING The following example uses the HAVING clause to specify which of the groups generated in the GROUP BY clause should be included in the result set. http: //technet. microsoft. com/en-us/library/ms 177673. aspx USE Adventure. Works 2012; GO SELECT DATEPART(yyyy, Order. Date) AS N'Year‘ , SUM(Total. Due) AS N'Total Order Amount‘ FROM Sales. Order. Header GROUP BY DATEPART(yyyy, Order. Date) ORDER BY DATEPART(yyyy, Order. Date); 1/2/2022 Pierce College CIS 261 SQL 7

This work is licensed under a Creative Commons Attribution 4. 0 International License Led by Bellevue College, the Health e. Workforce Consortium was formed to elevate Health Information Technology workforce development locally and nationally and provide career paths into this promising field for veterans and others. The ninecollege consortium includes Bellevue College, Bellingham Technical College, Clark College, Clover Park Technical College, Northern Virginia Community College, Pierce College, Renton Technical College, Spokane Community College, and Whatcom Community College. The Health Information and Management Systems Society (HIMSS) is also a primary partner. This workforce solution was 100% funded by an $11. 7 m grant awarded by the U. S. Department of Labor's Employment and Training Administration, Grant #TC-23745 -12 -60 -A-53. The solution was created by the grantee and does not necessarily reflect the official position of the U. S. Department of Labor. The Department of Labor makes no guarantees, warranties, or assurances of any kind, express or implied, with respect to such information, including any information on linked sites and including, but not limited to, accuracy of the information or its completeness, timeliness, usefulness, adequacy, continued availability or ownership. 1/2/2022 Pierce College CIS 261 SQL 8