Count total rows with a group by

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 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.

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

This will get you just the counts for your TRACKING_NUMs like you wanted, and also get the counts for every other amount of total orders (not just having order count = 4).

(It sounds like your query is for some kind of a report -- if that is the case, and if it will run often where performance is a concern, as you suggested -- you might as well get all of your values from one query, instead of changing or parameterizing the count that your are interested in and re-running it multiple times (even if that were automated). Much better to let the server do it all for you once. Forgive my assumption if that is not what you were working towards.)

I know that's not exactly what you were asking about... Remus Rusanu already nailed that, but you did ask for "any suggestions".