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.

FROM   (SELECT    p. [PatientSID]
        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'
WHERE [DisplayGroupSID] IN (1200003559
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
  • 1
    Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product (and your code is invalid standard SQL)
    – user1822
    Jan 24, 2018 at 21:52

1 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:

      ,COUNT(cnt.[DisplayGroupSID]) AS '# Of RTC Requests'
         LEFT JOIN #T cnt ON (ctr.[Sta3n] = cnt.[Sta3n])
 WHERE cnt.[DisplayGroupSID] IN
      ( 1200003559
 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.