Count total rows with a group by

sql-server, t-sql

Solution

SELECT COUNT(*)
FROM (
   SELECT TRACKING_NUM
   FROM Orders
   GROUP BY TRACKING_NUM
   HAVING count(distinct ORDER_NUM) = 4) AS Agg

Problem

I have the following query: ``` select count(ords.TRACKING_NUM) from Orders ords (NoLock) group by ords.TRACKING_NUM having count(distinct ords.ORDER_NUM) = 4 ``` I want it to retrieve the total amount of TRACKING_NUMs that have 4 ORDER_NUMs on them (should be 3,352). Instead I get 3,352 rows equal to 4 (or more because of the distinct). I get why this is happening. It is counting the values inside each group by. And I can easily change the query to this: ``` select ords.TRACKING_NUM from Orders ords (NoLock) group by ords.TRACKING_NUM having count(distinct ords.ORDER_NUM) = 4 ``` and then it get 3,352 rows of TRACKING_NUMs returned to me. However, this is not very performant on my database (takes about 41 seconds). What I really need is a query that will give me a count and only a count (and by doing that hopefully go faster). Thanks for any suggestions.

Original source