COUNT for instances with no value

count

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

Best Answer

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.

Related Solutions

Ms-access – “No Current Record” when using COUNT() in Access 2010

Okay, well, since nobody else chimed in and I needed to get it done, I took one last shot at it. Not quite sure on the why of it all, but I used basically the entire query as a subquery in the FROM clause, and then ran the report against that, and that seemed to do it...

SELECT
  [VendorCusts].[VendorPaid],
  [VendorCusts].[DE?],
  [VendorCusts].[IC?],
  [VendorCusts].[AG?],
  [VendorCusts].[GB?],
  COUNT(VendorCustID) AS [CustCount]
FROM
  (
    SELECT DISTINCT
      [Plan Revenue Expense].[Check To] AS [VendorPaid],
      [Support Provider].[DE?],
      [Support Provider].[IC?],
      [Support Provider].[Agency?] AS [AG?],
      [Support Provider].[GeneralBus?] AS [GB?],
      Customer.CustID AS [VendorCustID],
      Customer.LName AS [VendorCustLName],
      Customer.FName AS [VendorCustFName]
    FROM
      (((Customer
      INNER JOIN Plan ON Customer.CustID=Plan.CustID)
      INNER JOIN [Plan Revenue] ON [Plan].[Plan ID]=[Plan Revenue].[PlanID])
      INNER JOIN [Plan Revenue Expense] ON [Plan Revenue].[Rev ID]=[Plan Revenue Expense].[RevID])
      LEFT JOIN [Support Provider] ON [Plan Revenue Expense].[SP]=[Support Provider].[ID]
    WHERE
      (
        (
          ([Plan Revenue Expense].[First Day]>=[Expense Start "MM/DD/YYYY"])
          AND
          ([Plan Revenue Expense].[First Day]<=[Expense End "MM/DD/YYYY"])
        )
        OR
        (
          ([Plan Revenue Expense].[Last Day]>=[Expense Start "MM/DD/YYYY"])
          AND
          ([Plan Revenue Expense].[Last Day]<=[Expense End "MM/DD/YYYY"])
        )
      )
      AND NOT
      (
        [Plan Revenue].[Service]='111' OR
        [Plan Revenue].[Service]='222' OR
        [Plan Revenue].[Service] LIKE '333*'
      )
      AND NOT Customer.[Inactive?]=TRUE
    ORDER BY
      [Plan Revenue Expense].[Check To],
      Customer.LName,
      Customer.FName
  ) AS [VendorCusts]
GROUP BY
  [VendorCusts].[VendorPaid],
  [VendorCusts].[DE?],
  [VendorCusts].[IC?],
  [VendorCusts].[AG?],
  [VendorCusts].[GB?]
HAVING
  NOT ([VendorCusts].[DE?]=TRUE OR [VendorCusts].[IC?]=TRUE)
ORDER BY
  [VendorCusts].[DE?] ASC,
  [VendorCusts].[IC?] ASC,
  [VendorCusts].[AG?] ASC,
  [VendorCusts].[GB?] ASC,
  [VendorCusts].[VendorPaid] ASC;

This is more or less what I finally came up with and it appears to get the job done. Checked a few data points and they seemed to agree with the slightly more verbose version w/o a Count() function. So, I think the results are good.

Then I abstracted out the start and end of the date range so I could run a few different versions. And added a few other parameters I needed...

Seems like it's working as intended, if a bit kludge-y. Sometimes it's better to be 'right' than 'pretty.' ;)

Thesql: counting number of tickets which are open per day basis

You can use conditional sum for this calculation something as

select 
date_format(created,'%d-%m') as created_date , 
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;

Here is a test case

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open     |
|    2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new      |
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new      |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> select 
    -> date_format(created,'%d-%m') as created_date , 
    -> sum( case when status='open' then 1 else 0 end )+
    -> sum( case when status='new' then 1 else 0 end )+
    -> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
    -> from test
    -> where
    -> YEAR(Created) = '2015'
    -> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    2 |
+--------------+------+
2 rows in set (0.00 sec)

UPDATE The count of open is a incremental as per the comment. So the following should do the trick

select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select 
 date_format(created,'%d-%m') as created_date , 
 sum( case when status='open' then 1 else 0 end )+
 sum( case when status='new' then 1 else 0 end )+
 sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
 from test
 where
 YEAR(Created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

From the above sample data it will have

 +--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
+--------------+------+

UPDATE:

Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join something as

select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
 select 
 date_format(t.created,'%d-%m') as created_date , 
 sum( case when t.status='open' then 1 else 0 end )+
 sum( case when t.status='new' then 1 else 0 end )+
 sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 coalesce(tot,0) as resolved_ticket
 from test t left join(
    select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
 )x on date(x.resolved) = date(t.created) 
 where
 YEAR(t.created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

OUTPUT :

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
| 12-05        |    7 |
+--------------+------+

Best Answer

Related Solutions

Ms-access – “No Current Record” when using COUNT() in Access 2010

Thesql: counting number of tickets which are open per day basis

Related Question