CODEWITHSUNDEEP
joinaggregate
abagg1
abagg1

Reputation: 31

Join / Aggregate Function Query

I have the following code and output:

SELECT CustomerCategoryName, COUNT(a.CustomerID) AS CustomersInThisCategory
FROM Sales.Customers AS a
RIGHT JOIN Sales.CustomerCategories AS b on a.CustomerCategoryID = b.CustomerCategoryID
GROUP BY CustomerCategoryName
ORDER BY CustomersInThisCategory DESC

This generates the following output:

Output

When I add the following COUNT aggregate funcation and Inner Join:

SELECT CustomerCategoryName, COUNT(a.CustomerID) AS CustomersInThisCategory, COUNT(c.OrderID) AS Orders
FROM Sales.Customers AS a
RIGHT JOIN Sales.CustomerCategories AS b on a.CustomerCategoryID = b.CustomerCategoryID
INNER JOIN Sales.Orders AS c ON a.CustomerID = c.CustomerID
GROUP BY CustomerCategoryName
ORDER BY CustomersInThisCategory DESC

The output changes to:

Output2

I am not sure as to why the CustomersInThisCategory column is changing to the same as the Orders column? I'm also not sure why the results in the first ouput with 0 values are being removed in the second query as I still have the Right join present.

Any feedback would be much appreciated.

Upvotes: 0

Views: 36

Answers (1)

laavanya saba
laavanya saba

Reputation: 11

For your first query, count ( distinct a.customerId) should give you the unique customer ids in a category. Regarding your second question, right join is performed before inner join. So the inner join will splice the records, for which a match is not found. Hope my answer helps.

Upvotes: 1

Related Questions