AdventureWorks Database: Number of contacts for each type and name

SQL Query - AdventureWorks: Exercise-22 with Solution

22. From the following table write a query in SQL to count the number of contacts for combination of each type and name. Filter the output for those who have 100 or more contacts. Return ContactTypeID and ContactTypeName and BusinessEntityContact. Sort the result set in descending order on number of contacts.

Sample table: Person.BusinessEntityContact

Click to view Full table

Sample table: Person.ContactType

Click to view Full table

Sample Solution:

SELECT pc.ContactTypeID, pc.Name AS CTypeName, COUNT(*) AS NOcontacts
    FROM Person.BusinessEntityContact AS pbe
        INNER JOIN Person.ContactType AS pc
            ON pc.ContactTypeID = pbe.ContactTypeID
    GROUP BY pc.ContactTypeID, pc.Name
	HAVING COUNT(*) >= 100
    ORDER BY COUNT(*) DESC;

Sample Output:

contacttypeid|ctypename         |nocontacts|
-------------+------------------+----------+
           11|Owner             |       266|
           15|Purchasing Manager|       245|
           14|Purchasing Agent  |       242|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Retrieve the salesperson for each PostalCode.
Next: Formated date and weekly salary calculation.