1

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)
2

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.

  • I see your approach fails for some reason. IE. a phone number has many entries . See my edited question under Max. – Mannoj Mar 3 '13 at 8:10
0

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 |
+------------+----------+-------------+---------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.