Answers To Exercises: How to Group and Summarize your Results
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
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
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
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.