February 14, 2009 at 8:03 pm
I am guessing I am forgetting something pretty easy and basic but it's driving me nuts tonight. All I want to do is a count and a sum together. However when I put the sum in, it throws the counts way off.
For example in AdventureWorks if you wanted to know the number of sales persons for each territory, you would use this:
SELECT st.[Name], COUNT(sp.SalesPersonID) AS 'SalesPersonCount'
FROM Sales.SalesTerritory st
JOIN sales.SalesPerson sp ON sp.TerritoryID = st.TerritoryID
GROUP BY st.[Name]
Now I want to join to the sales orders table and report on the total sales for each of those regions.
SELECT st.[Name], COUNT(sp.SalesPersonID) AS 'salesPersonCount', SUM(soh.TotalDue) AS 'TotalSales'
FROM Sales.SalesTerritory st
JOIN sales.SalesPerson sp ON sp.TerritoryID = st.TerritoryID
JOIN sales.SalesOrderHeader soh ON sp.SalesPersonID = soh.SalesPersonID
AND soh.TotalDue <> 0
AND YEAR(soh.OrderDate) = 2004
GROUP BY st.[Name]
However, it throws the counts way off. I know this is a very basic issue but I cannot remember what I need to do to get this. Thanks for your help,
February 14, 2009 at 8:11 pm
Please try:
COUNT(DISTINCT sp.SalesPersonID)
February 14, 2009 at 8:39 pm
Thank you very much!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply