Execute the following script in Query Editor to obtain the contact info and total purchase activity for each vendor of AdventureWorks mountain, road & touring bike company:

USE AdventureWorks;

SELECT

    v.[Name] AS Vendor

   ,ct.[Name] AS [ContactType]

   ,c.[FirstName]+ ' ' + c.[LastName] AS ContactName

   ,c.[Phone]

   ,c.[EmailAddress]

   ,c.[EmailPromotion]

   ,[StateProvinceName] = sp.[Name]

   ,[CountryRegionName] = cr.[Name]

   ,'$'+convert(varchar,sum (poh.TotalDue),1)  AS TotalPurchaseAmount

   ,count( poh.PurchaseOrderID) AS TotalPurchaseCount

FROM [Purchasing].[PurchaseOrderHeader] poh

   INNER JOIN [Purchasing].[Vendor] v

   ON poh.VendorID = v.VendorID

   INNER JOIN [Purchasing].[VendorContact] vc

   ON vc.[VendorID] = v.[VendorID]

   INNER JOIN [Person].[Contact] c

   ON c.[ContactID] = vc.[ContactID]

   INNER JOIN [Person].[ContactType] ct

   ON vc.[ContactTypeID] = ct.[ContactTypeID]

   INNER JOIN [Purchasing].[VendorAddress] va

   ON va.[VendorID] = v.[VendorID]

   INNER JOIN [Person].[Address] a

   ON a.[AddressID] = va.[AddressID]

   INNER JOIN [Person].[StateProvince] sp

   ON sp.[StateProvinceID] = a.[StateProvinceID]

   INNER JOIN [Person].[CountryRegion] cr

   ON cr.[CountryRegionCode] = sp.[CountryRegionCode]

GROUP BY

    v.[Name]

   ,ct.[Name]

   ,c.[FirstName]+ ' ' + c.[LastName]

   ,c.[Phone]

   ,c.[EmailAddress]

   ,c.[EmailPromotion]

   ,sp.[Name]

   ,cr.[Name]

ORDER BY sum (poh.TotalDue) DESC;

GO