w3resource

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

T-SQL Cast versus Convert?

CONVERT is SQL Server specific, CAST is ANSI.

CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don't care about the extended features, use CAST.

Ref : https://bit.ly/3VKT5M8