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.

4 responses to “Answers To Exercises: How to Group and Summarize your Results”
  1. Ahmed Yisau

    Thank you for the tutorials. Very helpful.

  2. Bruce de Ridder

    Thank you for putting in the time to teach. Great help!

  3. Lee Mezzulo

    Gre

  4. Tafessech Tessema

    I cant thank you enough for making available this exercise, thank tou

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.