Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
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
add comment

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 |
+------------+----------+-------------+---------+
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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