How to count the difference in Informix SQL?

I’m counting number of rated calls and average rating in callcenter:

select  cr.queue, ROUND(AVG(TO_NUMBER(cd.datavalue)),2) as average, count(*) as count
from callrecord cr
left join calldata cd on cd.callid=cr.callid 
where cd.datakey="qrate1" 
group by queue

|queue    |average   |count   |
+---------+----------+--------+
|sales    |3.92      |12      |
|service  |3.75      |4       |

(12 rated calls for sales and 4 rated calls for service).

I can also count number of total calls (rated and unrated):

select  cr.queue, 0 as average, count(*) as count
from callrecord cr
group by queue

|queue    |average   |count   |
+---------+----------+--------+
|sales    |0         |21      |
|service  |0         |4       |

(21 total calls for sales and 4 total calls for service).

But I want to count only unrated calls. SQL request:

select  cr.queue, 0 as average, count(*) as count
from callrecord cr
left join calldata cd on cd.callid=cr.callid 
where cd.datakey!="qrate1" 
group by queue

runs slowly and produce incorrect result, e.g.

|queue    |average   |count   |
+---------+----------+--------+
|sales    |0         |69      |
|service  |0         |16      |

(69 unrated calls for sales and 16 unrated calls for service – incorrect).

Hence count of unrated = total – rated, I can’t build an SQL where I can get this result.

Desired result should be:

|queue    |average   |count   |
+---------+----------+--------+
|sales    |0         |9       |
|service  |0         |0       |

(21-12=9 unrated calls for sales and 4-4=0 unrated calls for service).

Example of CALLDATA table:

|callid   |datakey   |datavalue  |
+---------+----------+-----------+
|181      |ANI       |1234567890 |
|181      |DNIT      |2345678901 |
|181      |IVR_CHOICE|SALES      |
|182      |ANI       |1234567890 |
|182      |DNIT      |2345678901 |
|182      |QRATE1    |1          |
|183      |ANI       |1234567890 |
|183      |DNIT      |2345678901 |
|183      |LANG      |ENGLISH    |

Finally I have prepared a script for dbfiddle.uk to play with this scenario:

select *
into calldata
from (values ('181','ANI','1234567890')
     , ('181','DNIT','2345678901')
     , ('181','IVR_CHOICE','SALES')
     , ('182','ANI','1234567890')
     , ('182','DNIT','2345678901')
     , ('182','QRATE1','1')
     , ('183','ANI','1234567890')
     , ('183','DNIT','2345678901')
     , ('183','LANG','ENGLISH') ) z(callid,datakey,datavalue);

select *
into callrecord
from (values ('181','SALES')
     , ('182','SALES' )
     , ('183','SALES' ) ) z(callid,queue);     
GO

12 rows affected

select queue, count(*) as total
from callrecord
group by queue
GO

queue | total
:---- | ----:
SALES |     3

select cr.queue, count(*) as rated
from callrecord cr
left join calldata cd on cr.callid=cd.callid
where cd.datakey='QRATE1'
group by queue
GO

queue | rated
:---- | ----:
SALES |     1

select cr.queue, count(*) as unrated
from callrecord cr
left join calldata cd on cr.callid=cd.callid
where cd.datakey<>'QRATE1'
group by queue
GO

queue | unrated
:---- | ------:
SALES |       8

select cr.queue, SUM(CASE WHEN cd.datakey='QRATE1' THEN 0 ELSE 1 END) as unrated
from callrecord cr
left join calldata cd on cr.callid=cd.callid
group by queue
GO

queue | unrated
:---- | ------:
SALES |       8

db<>fiddle here

Answer

Since you do not want to count the child table, calldata, matched records but only callrecord level records, consider using EXISTS clause (or IN) with correlated subquery:

-- EXISTS
select cr.queue, count(*) as rated
from callrecord cr
where exists (
  select 1 from calldata cd
  where cd.callid = cr.callid
  and cd.datakey = 'QRATE1'
)
group by queue


-- NOT EXISTS
select cr.queue, count(*) as rated
from callrecord cr
where not exists (
  select 1 from calldata cd
  where cd.callid = cr.callid
  and cd.datakey = 'QRATE1'
)
group by queue

Online Demo

Advertisement

Answer