AdventureWorks Database: Number of contacts for each type and name

Last update on December 20 2024 11:57:31 (UTC/GMT +8 hours)

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.

businessentityid|personid|contacttypeid|rowguid                             |modifieddate           |
----------------+--------+-------------+------------------------------------+-----------------------+
             292|     291|           11|7d4d2dbc-4a44-48f5-911d-a63abafd5120|2017-12-13 13:21:02.243|
             294|     293|           11|3ea25b65-9579-4260-977d-d6f00d7d20ee|2017-12-13 13:21:02.320|
             296|     295|           11|dadac1ff-3351-4827-9ae0-95004885c193|2017-12-13 13:21:02.383|
             298|     297|           11|b924f26f-6446-45d1-a92b-6f418374f075|2017-12-13 13:21:02.447|
             300|     299|           11|5ba4e7be-8d29-46a2-b68d-67b1615b124a|2017-12-13 13:21:02.510|
             302|     301|           11|6cc8f248-8d96-4afd-adcc-61d93e8de3b1|2017-12-13 13:21:02.570|
             304|     303|           11|33b4da81-4448-481f-bf7c-357ea4d23f21|2017-12-13 13:21:02.633|
             306|     305|           11|70d35526-7c2f-470f-98cb-f9299a754f16|2017-12-13 13:21:02.697|
             308|     307|           14|da33b75d-32fb-432d-a275-9e9d32e78f3e|2017-12-13 13:21:02.760|
             310|     309|           14|c2dee145-a902-477e-ab21-29659ac3e97e|2017-12-13 13:21:02.820|
             312|     311|           14|4020deae-56f6-47df-96c1-a01f3de2305c|2017-12-13 13:21:02.883|
             314|     313|           14|9ef858de-eaba-4a53-9b28-fc699d789957|2017-12-13 13:21:02.930|
			 -- more --

Click to view Full table

contacttypeid|name                           |modifieddate           |
-------------+-------------------------------+-----------------------+
            1|Accounting Manager             |2008-04-30 00:00:00.000|
            2|Assistant Sales Agent          |2008-04-30 00:00:00.000|
            3|Assistant Sales Representative |2008-04-30 00:00:00.000|
            4|Coordinator Foreign Markets    |2008-04-30 00:00:00.000|
            5|Export Administrator           |2008-04-30 00:00:00.000|
            6|International Marketing Manager|2008-04-30 00:00:00.000|
            7|Marketing Assistant            |2008-04-30 00:00:00.000|
            8|Marketing Manager              |2008-04-30 00:00:00.000|
            9|Marketing Representative       |2008-04-30 00:00:00.000|
           10|Order Administrator            |2008-04-30 00:00:00.000|
           11|Owner                          |2008-04-30 00:00:00.000|
           12|Owner/Marketing Assistant      |2008-04-30 00:00:00.000|
		   -- more --

Click to view Full table

Sample Solution:

-- Selecting contact type ID, contact type name, and the count of contacts from multiple tables
SELECT 
    -- Selecting the contact type ID
    pc.ContactTypeID, 
    -- Renaming the Name column as CTypeName for better readability
    pc.Name AS CTypeName, 
    -- Counting the number of contacts for each contact type
    COUNT(*) AS NOcontacts
-- Joining Person.BusinessEntityContact table with Person.ContactType table based on specific conditions
FROM Person.BusinessEntityContact AS pbe
    INNER JOIN Person.ContactType AS pc
        ON pc.ContactTypeID = pbe.ContactTypeID
-- Grouping the result set by contact type ID and name
GROUP BY pc.ContactTypeID, pc.Name
-- Filtering the groups to include only those with a count of contacts greater than or equal to 100
HAVING COUNT(*) >= 100
-- Sorting the result set by the count of contacts in descending order
ORDER BY COUNT(*) DESC;

Explanation:

• The SELECT statement retrieves data from multiple tables.
• pc.ContactTypeID, pc.Name AS CTypeName, COUNT(*) AS NOcontacts: Specifies the columns to be retrieved, including the contact type ID, the renamed contact type name, and the count of contacts.
• The FROM clause specifies the source tables for the data, including Person.BusinessEntityContact (aliased as pbe) and Person.ContactType (aliased as pc).
• The INNER JOIN clause joins the tables based on specific conditions (pc.ContactTypeID = pbe.ContactTypeID).
• The GROUP BY clause groups the result set by contact type ID and name.
• The HAVING clause filters the groups to include only those with a count of contacts greater than or equal to 100.
• ORDER BY COUNT(*) DESC: Sorts the result set by the count of contacts in descending order.

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.