1

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
  • 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) – a_horse_with_no_name Jan 24 '18 at 21:52
2

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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