SELECT sp.StateProvinceCode, COUNT(DISTINCT (c.StoreID)) as NStores, SUM(soh.TotalDue) AS Total
FROM Sales.SalesOrderHeader AS soh INNER JOIN
Sales.Customer AS c ON soh.CustomerID = c.CustomerID INNER JOIN
Person.BusinessEntity AS b ON b.BusinessEntityID = c.StoreID INNER JOIN
Sales.Store AS st ON st.BusinessEntityID = b.BusinessEntityID INNER JOIN
Person.BusinessEntityAddress AS a ON a.BusinessEntityID = b.BusinessEntityID INNER JOIN
Person.Address AS ad ON ad.AddressID = a.AddressID INNER JOIN
Person.AddressType AS at ON at.AddressTypeID = a.AddressTypeID INNER JOIN
Person.StateProvince AS sp ON sp.StateProvinceID = ad.StateProvinceID
WHERE (c.StoreID IS NOT NULL) AND (at.Name = N'Main Office') AND (sp.CountryRegionCode = N'US')
GROUP BY sp.StateProvinceCode
ORDER BY sp.StateProvinceCode |