Mysql – Need logic to collate data

MySQL

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)

Best Answer

I inadvisable to use Datetime type for your answer_time, change it to TIME type because the 000-00-00 will fail with all datetime functiosn (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.