Answers To Exercises: How to Group and Summarize your Results

Question #1

HR wants a report of the number of active employees by job title.  What SQL would you use?

SELECT   JobTitle,
         Count(JobTitle)
FROM     HumanResources.Employee
GROUP BY JobTitle

Question #2

Display the Min, Max, and Average Quantity ordered for each product in SalesOrderDetails

SELECT   ProductID,
         Min(OrderQty),
         Max(OrderQty),
         Avg(OrderQty)
FROM     Sales.SalesOrderDetail
GROUP BY ProductID

Question #3

List all employee job titles, and number of employees where the average number of sick leave hours is less than or equal to forty.

SELECT JobTitle,
Count(JobTitle)
FROM HumanResources.Employee
GROUP BY JobTitle
HAVING Avg(sickleavehours) <= 40

Question #4

For a job title returned in #3 above, is the count the same for the corresponding job title answer #1’s result?

Yes, the counts are the same.  The HAVING clause doesn’t restrict which row are included inside of a group, the WHERE clause, does that.  Rather, the HAVING clause is used to determine whether a group as a whole is eligible to be included in the results.