Counting values in subquery to create column in main query (??)

[elsenorjose]

I have a table tblCALL which stores a unique ID for a call, when the call made a request of the CTI (computer telephony interface)(datetime value in CTIREQUEST field) and whether the call was answered or not (datetime value in the ANSWERED column or NULL if not answered). I need to create a daily grouping of calls offered (a count of all unique IDs since ALL calls get one) a count of 'lost' or abandoned calls (a count of calls with NULL in the ANSWERED column) and the percentage of abandons to calls offered.

So, in my limited knowledge of MySQL, I tried this:

SELECT COUNT(C.ID) 'CALLS',
COUNT(A.ID) 'ABANDONS'
SUM(COUNT A.ID)/SUM(COUNT C.ID) 'ABANDON %'
FROM tblCALL C
LEFT JOIN tblCALL A
ON C.ID = A.ID
WHERE A.ANSWERED IS NULL

I am getting a result for calls (23,453) but nothing for abandons even though I know there are several. I'll clarify further if this is not enough but I hope what I'm trying to accomplish is clear in my example. Thanks.

 

[r937]

select count(*) as calls
     , count(*) - count(a.id) as abandons
     , ( count(*) - count(a.id) )
          / count(*) as abandon_pct
  from tblcall

r937.com | rudy.ca

 

[elsenorjose]

Ok, so that didn't work. Maybe I'm asking the wrong question. As I read the MySQL reference and other books, I find that what I want is a multi-row resultset based on a subquery of join. For example, that query returned one row with my total calls and 0 abandons. That's not correct because based on the results of: select count(*) from tblCALL where call_answer_time is null I get ~2100 records which are my abandons.

What I need is a resultset that gives me 1 column for date, 1 column for total calls, and 1 item for abandons (I can calculate the abandon percentage in a spreadsheet). The results need to be grouped by date so I'm looking for something like this: (This is the result of 2 queries which is why the abandon number is in there):


Date Calls Abandons
2005-10-02 3463 106
2005-10-01 3686 96
2005-09-30 2423 204
2005-09-29 2001 221
2005-09-28 3730 614
2005-09-27 1996 112
2005-09-26 1629 50
2005-09-25 2767 137
2005-09-24 3043 364
2005-09-23 1814 56
2005-09-22 1543 53
2005-09-21 1561 83
2005-09-20 1225 40
2005-09-19 1098 34
2005-09-18 2002 70
2005-09-17 2105 96
2005-09-16 1475 77
2005-09-15 1966 95
2005-09-14 240 25
2005-09-13 239 12
2005-09-12 254 17
2005-09-11 253 7
2005-09-10 254 8
2005-09-09 254 15
2005-09-08 254 17
2005-09-07 254 21


So basically, from the same table I need a count of all records where CALL_ANSWER_TIME is NULL (for my ABANDONS column) and a count of all records regardless of CALL_ANSWER_TIME for my total calls offered.

Here are my two queries:
(Total Calls)
select date(call_request_time) 'Date',
count(uuid) 'Calls'
from CALL_AT_CTI
group by date(call_request_time)
order by date(call_request_time) desc

(Abandons)
select date(call_request_time) 'Date',
count(uuid) 'Abandons'
from CALL_AT_CTI
where call_answer_time is null
group by date(call_request_time)
order by date(call_request_time) desc


Thanks again for your help.

 

[r937]

select date(call_request_time) 'Date'
     , count(uuid) 'Calls'
     , sum(case when call_answer_time is null
                then 1 else 0 end) as 'Abandons'
  from CALL_AT_CTI
group 
    by date(call_request_time)
order 
    by date(call_request_time) desc

r937.com | rudy.ca

 

[elsenorjose]

This is weird, I still keep getting 0 for abandons but like I said, there are records in that table with NULL CALL_ANSWER_TIMEs. Any ideas?

 

[elsenorjose]

Ok, so when I use '...call_answer_time is NULL' in the statement, I get nothing but when I specify '...call_answer_time = '0000-00-00 00:00:00' I get the correct counts. Thanks for the help. I can now get on with my life!

 

[r937]

yes, that's the answer, your column probably doesn't actually allow NULLs

ain't standard sql behaviour grand?





r937.com | rudy.ca

 

[elsenorjose]

Well, this is probably good for me. I need to stop relying on non-standard SQL (T-SQL and PL/SQL are the flavors I am familiar with) and learn the basics

Thanks again for your help!