CODEWITHSUNDEEP
mysqlsql
gedq
gedq

Reputation: 610

how do I replace missing records with nulls in a mysql query?

I get the number of tests taken by a unit :

  select 
    date(START_DATE_TIME), product_id, BATCH_SERIAL_NUMBER, count(*) 
from 
    ( select START_DATE_TIME, product_id, uut_serial_number, BATCH_SERIAL_NUMBER 
      from uut_result    
      where START_DATE_TIME >= '2016-07-01 00:00:00' 
      and START_DATE_TIME <= '2016-07-07 23:59:59') as passtbl
      group by date(START_DATE_TIME), product_id, batch_serial_number;

I fetch the number of tests a unit passed broken down by day:

  select 
    date(START_DATE_TIME), product_id, BATCH_SERIAL_NUMBER, count(*) 
from 
    ( select START_DATE_TIME, product_id, uut_serial_number, BATCH_SERIAL_NUMBER 
      from uut_result    
      where START_DATE_TIME >= '2016-07-01 00:00:00' 
      and START_DATE_TIME <= '2016-07-07 23:59:59'     
      and uut_status = 'passed' ) as passtbl
      group by date(START_DATE_TIME), product_id, batch_serial_number;

what I'm finding is that there are units that don't have any pass records at all, so the second query is returning fewer records than the first. This is breaking post processing. Is there a way to catch the absence of a record and replace it with null or some other dummy value?

Upvotes: 0

Views: 78

Answers (2)

Darrel Lee
Darrel Lee

Reputation: 2470

My standard answer to everything like this is to use a common table expression and window functions, instead of using group by where you lose the details and have to struggle to recover them.

To get a dummy row you might use a union like this:

;with myCTE (unitId, otherdetail, passed)
as (
   select unitDetail, otherdetail,  Sum(1) Over (partition by unit) as passed
   from sourceTable
)
SELECT unitid, otherDetail, passed 
from myCTE
where startDate >= lowerbound and startdate < upperBound

UNION
SELECT unitId, otherdetail, 0 as passed
from sourceTable S
where not exists (select 1 from myCTE where myCTE.unitId = S.unitID
        and  startDate >= lowerbound and startdate < upperBound)

I think that's a pretty good rough sketch of what you need. Also I would used a half-open interval to compare times On the off chance that the startTime is between 11:59:59 and 0:00 the next day.

You never mentioned what db engine [Duh, it's in the title I was looking for a TAG]. CTE is available on SQL Server and Oracle, but not on MySQL.

For most uses you can substitute a correlated subquery,but you have to repeat yourself. The ';' before WITH is a quirk of SQL Server.

Since you are MySQL, you have to duplicate the CTE as a subquery where it is referenced. Or maybe you have table-valued functions??

Upvotes: 0

Gilad Green
Gilad Green

Reputation: 37299

select date(START_DATE_TIME), 
       product_id, 
       BATCH_SERIAL_NUMBER, 
       status, 
       count(*) 
from (select *, 
          case when uut_status = 'passed' then uut_status 
               else 'other statuses'
               end status
      from uut_result)    
where START_DATE_TIME >= '2016-07-01 00:00:00' 
and START_DATE_TIME <= '2016-07-07 23:59:59'
group by date(START_DATE_TIME), 
         status, 
         product_id, 
         batch_serial_number;

Upvotes: 1

Related Questions