Read full lines with group

Question

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 him to get the total number of TRACKING_NUM that have 4 ORDER_NUM on them (should be 3,352). Instead, I get 3,352 rows equal to 4 (or more due to differences).

I understand why this is happening. It counts the values ​​within each group. And I can easily change the request for this:

select  ords.TRACKING_NUM
from    Orders ords (NoLock)   
group by ords.TRACKING_NUM
having count(distinct ords.ORDER_NUM) = 4

and then it returns me 3,352 rows of TRACKING_NUM. However, this does not work very well in my database (takes about 41 seconds). What I really need is a request that will give me an account and only an account (and that, I hope, will be faster).

Thanks for any suggestions.

Answer 1

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

Answer 2

SELECT OrderCount AS 'Total Orders', COUNT(TRACKING_NUM) AS 'Tracking Num Count' 
FROM (
   SELECT DISTINCT TRACKING_NUM, COUNT(DISTINCT ORDER_NUM) AS 'OrderCount'
   FROM Orders
   GROUP BY TRACKING_NUM
) AS tblOrdersPerTrackingNum

TRACKING_NUM, , ( = 4).

(, - - , , , - , , , , ( ). , . , , .)

, , ... , " ".