COUNT for instances with no value

Question

I have a query that looks for and counts orders that have been written in a medical record that have a specific display group SID. I have 8 medical centers and I want a count for all of them even if it's zero. My query return counts fine when there are instances, but it will not list a facility with a zero count. I tried the LEFT JOIN as mentioned in other threads.

SELECT DISTINCT *
INTO   #T
FROM   (SELECT    p. [PatientSID]
                  ,p.[TestPatientFlag]
                  ,co.[CPRSOrderSID]
                  ,co.[DisplayGroupSID]
                  ,co.[Sta3n]
                  ,[EnteredDateTime]
        FROM      [CPRSOrder].[CPRSOrder] co
        LEFT JOIN [SPatient].[SPatient] p 
        ON        co.[PatientSID] = p.[PatientSID]
        WHERE     [TestPatientFlag] IS NULL 
        AND       [EnteredDateTime] >'2018-01-01'
        ) AS X

SELECT [Sta3n]
       ,COUNT([DisplayGroupSID]) AS '# Of RTC Requests'
FROM   #T
WHERE [DisplayGroupSID] IN (1200003559
                            ,1200003561
                            ,1200003558
                            ,1200003557
                            ,1200003560
                            ,1200003562
                            ,1200003556
                            ,1200003519)
GROUP BY [Sta3n]

Here's the output (It's missing 4 sites who have no instance of the type of order I am looking for:

Sta3n   # Of RTC Requests
-----   -----------------
679     755
544     974
521     2
534     54

Answer 1

You need to use the LEFT JOIN to connect a dataset with the list of values you want to see with another dataset with the values you want a count of.

If you make your final query something like:

SELECT
       ctr.[Sta3n]
      ,COUNT(cnt.[DisplayGroupSID]) AS '# Of RTC Requests'
  FROM (SELECT DISTINCT [Sta3n] FROM [CPRSOrder]) ctr
         LEFT JOIN #T cnt ON (ctr.[Sta3n] = cnt.[Sta3n])
 WHERE cnt.[DisplayGroupSID] IN
      ( 1200003559
       ,1200003561
       ,1200003558
       ,1200003557
       ,1200003560
       ,1200003562
       ,1200003556
       ,1200003519)
 GROUP BY ctr.[Sta3n]
;

(Alias ctr is short for "medical CenTeR"; cnt for "values to CouNT").

You should get something more like what you are looking for.

Note that it may make more sense to select the list of distinct [Sta3n] values from some other location - you presumably know more about your system than I do.

Similarly, the temp table you're using may not be necessary, and you might choose to store the distinct [Sta3n] values in their own temp table.

The key is, have the list of values you want to see even if your count is 0 in the table on the left side of the join, and the table with the data to be counted on the right side.

NOTE: Assumes your DBMS allows sub-queries as derived tables, and table name aliases. I think that's pretty generic, but you don't specify your DBMS.