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.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

How to avoid the "divide by zero" error in SQL?

Select Case when divisor=0 then null
Else dividend / divisor
End ,,,

OR:

Select dividend / NULLIF(divisor, 0) ...

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

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook