Search code examples
sqlsql-servert-sqladventureworks

SQL COUNT in different tables

For each Vendor I want to sum -number of products the vendor sells -number of orders (PurchaseOrderID) -order value (SubTotal)

This Is what my Database looks like

enter image description here

This is where I'm at now

USE AdventureWorks2014
GO 
SELECT pv.Name, 
Continent = CASE pc.Name
WHEN 'United States' THEN 'Nordamerika'
WHEN 'England' THEN 'Europa'
WHEN 'Germany' THEN 'Europa'
WHEN 'France' THEN 'Europa'
END,
(SELECT COUNT(ProductID) FROM Purchasing.ProductVendor) NumberOfProducts,
(SELECT COUNT(PurchaseOrderID) FROM Purchasing.PurchaseOrderHeader) NumberOfOrders 
FROM Purchasing.Vendor pv
INNER JOIN Person.BusinessEntity pbe ON pv.BusinessEntityID = pbe.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress pbea ON pbe.BusinessEntityID = pbea.BusinessEntityID
INNER JOIN Person.Address pa ON pbea.AddressID = pa.AddressID
INNER JOIN Person.StateProvince psp ON pa.StateProvinceID = psp.StateProvinceID
INNER JOIN Person.CountryRegion pc ON psp.CountryRegionCode = pc.CountryRegionCode  
INNER JOIN Purchasing.ProductVendor ppv ON pv.BusinessEntityID = ppv.BusinessEntityID
INNER JOIN Purchasing.PurchaseOrderHeader ppoh ON pv.BusinessEntityID = ppoh.VendorID 
GROUP BY pv.Name, pc.Name

The result is not what I want. I want the count to be for each vendor.

Thanks

Solution

  • I assume part of the problem is the 1:M relationship causing artificially inflated counts. To resolve you could use derived tables or analytic functions. Using derived tables:

    SELECT pv.Name, 
Continent = CASE pc.Name
WHEN 'United States' THEN 'Nordamerika'
WHEN 'England' THEN 'Europa'
WHEN 'Germany' THEN 'Europa'
WHEN 'France' THEN 'Europa'
END,
CntProd NumberOfProducts,
cntPO NumberOfOrders 
FROM Purchasing.Vendor pv
INNER JOIN Person.BusinessEntity pbe ON pv.BusinessEntityID = pbe.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress pbea ON pbe.BusinessEntityID = pbea.BusinessEntityID
INNER JOIN Person.Address pa ON pbea.AddressID = pa.AddressID
INNER JOIN Person.StateProvince psp ON pa.StateProvinceID = psp.StateProvinceID
INNER JOIN Person.CountryRegion pc ON psp.CountryRegionCode = pc.CountryRegionCode  
INNER JOIN (SELECT count(ProductID) CntProd, BusinessEntityID 
            FROM Purchasing.ProductVendor 
            GROUP BY BusinessEntityID) ppv ON pv.BusinessEntityID = ppv.BusinessEntityID
INNER JOIN (SELECT count(PurcahseOrderID) CntPO, VendorID
            FROM Purchasing.PurchaseOrderHeader 
            GROUP BY VendorID) ppoh ON pv.BusinessEntityID = ppoh.VendorID

    Using window functions (though I'm not sure about the results here) given your case statement for continent vs the group by on the vendor name and vendor region vs continent. may need to adjust the group by to:

    GROUP BY pv.Name, CASE pc.Name
                  WHEN 'United States' THEN 'Nordamerika'
                  WHEN 'England' THEN 'Europa'
                  WHEN 'Germany' THEN 'Europa'
                  WHEN 'France' THEN 'Europa'
                  END

    .

    SELECT pv.Name, 
Continent = CASE pc.Name
WHEN 'United States' THEN 'Nordamerika'
WHEN 'England' THEN 'Europa'
WHEN 'Germany' THEN 'Europa'
WHEN 'France' THEN 'Europa'
END,
COUNT(ProductID) over (partition by PPV.BusinessEntityID) NumberOfProducts,
COUNT(PurchaseOrderID) over (partition by ppoh.VendorID) NumberOfOrders 
FROM Purchasing.Vendor pv
INNER JOIN Person.BusinessEntity pbe ON pv.BusinessEntityID = pbe.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress pbea ON pbe.BusinessEntityID = pbea.BusinessEntityID
INNER JOIN Person.Address pa ON pbea.AddressID = pa.AddressID
INNER JOIN Person.StateProvince psp ON pa.StateProvinceID = psp.StateProvinceID
INNER JOIN Person.CountryRegion pc ON psp.CountryRegionCode = pc.CountryRegionCode  
INNER JOIN Purchasing.ProductVendor ppv ON pv.BusinessEntityID = ppv.BusinessEntityID
INNER JOIN Purchasing.PurchaseOrderHeader ppoh ON pv.BusinessEntityID = ppoh.VendorID 
GROUP BY pv.Name, pc.Name