CODEWITHSUNDEEP
mysql
JimmyF
JimmyF

Reputation: 44

MYSQL Query Help: Results are not 100 percent accurate

I have a mysql database that contains Call Detail Records. In this particular query i'd like to search all the calls for the reason the call was disconnected and return a list of disconnect reasons and the count of how many times each one occurred.

An example result set would look something like this:

 causeCode | count
           |
 0         | 380
 16        | 289
 47        | 2

One record represents a single call. There are two fields for each record that contains the disconnect reason, origCause_value which specifies the disconnect reason for the calls disconnected by the originator and destCause_value which specifies the disconnect reason for calls disconnected by the called party.

The query i'm using works pretty well for the most part however the issue is this. At first I thought that each record would only have 1 value in either origCause_value or destCause_valuedepending on which party disconnected the call. I'm finding out that while this is true most of the time, some records have both fields filled in. This throws off my count so its not 100 percent accurate.

For example:

 Above shows that cause code 47 was used 2 times.  
 Technically this is correct because in one record it is in both the
 origCause_value and destCause_value field.  


However the results i'd like to see returned would be 1.  
To indicate the  number of calls that were terminated with cause code 47

Here is the query i'm using:

 SELECT c.causeCode, sum(count) as count
 FROM ((SELECT origCause_value as causeCode, COUNT(origCause_value) AS count
   FROM CDR
   GROUP BY causeCode
  ) UNION ALL
  (SELECT  destCause_value as causeCode, COUNT(destCause_value) AS count
   FROM CDR
   GROUP BY causeCode
  )
 ) c
 GROUP BY c.causeCode ORDER BY count DESC

I'm looking for help to see if there is a way I can filter this down more to get the desired results.

Example Table:

 id  | CallingPartyNumber | finalCalledPartyNumber | origCause_value | destCause_value
 1              5551212             7771212             16              0
 2              5551212             7771212             0               16
 3              7771212             5551212             47              47
 4              7771212             5551212             16              0
 5              5551212             777121              16              0

Thanks in advance.

Upvotes: 1

Views: 82

Answers (1)

O. Jones
O. Jones

Reputation: 108641

47 (no resources) is a weird cause code. Both ends can disconnect at the same time when this happens.

Most calls have a disconnection cause code from only one end. Your CDR table shows a cause of zero from the end that didn't cause the disconnection.

You obviously want to have the same number of disconnect causes as you have rows in the CDR, or you'll get confusion. 

   SELECT COUNT(*) cause_count, 
          CASE WHEN origCause_value <> 0 
               THEN origCause_value
               ELSE destCause_value
                END cause_value
     FROM CDR
    GROUP BY CASE WHEN origCause_value <> 0 
                  THEN origCause_value
                  ELSE destCause_value
                   END 
    ORDER BY COUNT(*) DESC

Here's a demonstration of this query. http://sqlfiddle.com/#!9/a8527/1/0

The CASE statement grabs the nonzero cause code from each record.

You could also use GREATEST(origCause_value, destCause_value) but it's probably less robust.

(I edited this answer; my previous use of COALESCE() assumed the unset cause code was NULL, not zero).

Edit

Your new sample (http://sqlfiddle.com/#!9/9f3ba/1) has, as its second record, this:

 (2,5551212,7771212,393216,16),

What does this mean? Should this be counted as a "16" -- a normal call termination from the receiving end? Or does the 393216 value (0x60000) have some kind of special meaning?

Getting your counts right probably means you need to understand the meaning of these pairs of cause codes. The query I gave you will count that CDR as 393216, because it picks up the first number, not the second.

Upvotes: 1

Related Questions