How do I find the distinct count of a combination of values in a group? For example, to obtain the number of distinct customers who each year placed orders recorded in the Orders table in the Northwind database, I can run the query that Listing 1 shows. Figure 1 contains the output that Listing 1's query produces. But what if I need the number of different combinations of customers who placed orders and employees who took orders per year? When I add EmployeeID to the DISTINCT COUNT statement in Listing 1's query, as the following code shows, the query returns an error:
SELECT
YEAR(OrderDate) AS OrderYear,
COUNT(DISTINCT CustomerID, EmployeeID) AS NumCustsEmps
FROM Orders
GROUP BY YEAR(OrderDate)
How can I retrieve the data I'm looking for?
Your query doesn't work because T-SQL doesn't allow multiple columns inside the COUNT() function. You can use a derived table to retrieve the data you need. The query at callout A in Listing 2 returns a row for each unique combination of OrderYear, CustomerID, and EmployeeID. Now, you only need to turn the query into a derived table and perform another GROUP BY operation based on the year alone, as Listing 2 shows. Using a COUNT(*) operation on the rows gives you the number of unique combinations of customers and employees in each year. Figure 2 shows the output that Listing 2 generates.