Need logic to collate data

Question

I have a table in the below structure.

phone bigint
answer_time datetime

I should group all phone numbers, and its total number of attempts and mention its no:of time answered and no:of time not answered. For this the condition is answer_time='0000-00-00 00:00:00' for not answered and answer_time > '0000-00-00 00:00:00' for answered. There can be any number of times the call has been made to a particular number. Any suggestions or approach would be helpful. I'm terribly stuck up on this.

The result should be :

+----------------+---------------+--------------------+--
| phone         | count(*) | NOT_ANSWERED | ANSWERED   |
+---------------+----------+------------+------------+-----+
| 7260513       |      2   |       1      |  1       |
+------------+----------+------------+--------------------+

In this, at the first attempt the call is answered at the second it is not. The attempts may range max to 16 times.

FOR MAX

mysql> select phone, answer_time from cdr_test;
+------------+---------------------+
| phone   | answer_time         |
+------------+---------------------+
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
| 31389966 | 0000-00-00 00:00:00 |
+------------+---------------------+
13 rows in set (0.00 sec)

mysql> select phone, Count(*), Count(CASE WHEN Time(answer_time) = 0 THEN 1 end ) AS  NOT_ANSWERED, Count(CASE WHEN Time(answer_time) > 0 THEN 1 end) AS ANSWERED  FROM   cdr_test  GROUP  BY phone;
+------------+----------+--------------+----------+
| phone      | Count(*) | NOT_ANSWERED | ANSWERED |
+------------+----------+--------------+----------+
| 31389966 |       13 |            0 |        0 |
+------------+----------+--------------+----------+

1 row in set (0.00 sec)

Answer 1

I advise you to not use Dateime type for your answer_time, change to TIME type because the 000-00-00 will fail with all datetime function (look at the MySQL documentation : link).

So, when your table looks like :

[09:47:55] [DEV\MFO] mysql> desc MFO.phone_call;
+-------------+------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| phone       | bigint(20) | YES  |     | NULL    |       |
| answer_time | datetime   | YES  |     | NULL    |       |
+-------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

You could try that :

SELECT phone, 
   Count(*), 
   Count(CASE 
           WHEN Time(answer_time) = 0 THEN 1 
         end) AS NOT_ANSWERED, 
   Count(CASE 
           WHEN Time(answer_time) > 0 THEN 1 
         end) AS ANSWERED 
FROM   MFO.phone_call 
GROUP  BY phone; 

+-------+----------+--------------+----------+
| phone | count(*) | NOT_ANSWERED | ANSWERED |
+-------+----------+--------------+----------+
| 11111 |       21 |           10 |       11 |
| 22222 |        2 |            0 |        2 |
+-------+----------+--------------+----------+
2 rows in set (0.00 sec)

Max.

Answer 2

This needs an add-on for Max approach.

select phone, count(*),  count(CASE WHEN if(answer_time='0000-00-00 00:00:00',0,1) = 0  then 1 end ) AS NOT_ANSWERED , count(CASE WHEN if(answer_time!='0000-00-00 00:00:00',0,1) = 0  then 1 end ) AS ANSWERED FROM cdr_data group by phone;  

+------------+----------+-------------+----------+
| phone      | count(*) | NOT_ANSWERED| ANSWERED |
+------------+----------+-------------+----------+
| 31389966   |       13 |    13       |       0 |
+------------+----------+-------------+---------+