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
Sample table: Person.ContactType
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.
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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
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