Get average in mysql with different conditions

Navigation
0

I am putting together a graph containing the number of CHATS that were answered at a given time, eg:

  

1-30s - > 20 chats answered

     

31-60s - > 66 chats answered

     

60-120s - > 4 chats answered

I'm confused on how to return this using a query

The base structure of my table is:

  

ID | Contact Us | Chat duration

     

1 | Test | 20

     

2 | Test2 | 66

To get the average duration, I use the code below: 

$filter['filtergt']['user_id'] = 0;           
$filterCombined = array_merge_recursive($filter,array('filtergt' => array('chat_duration' => 0),'filter' =>  array('status' => erLhcoreClassModelChat::STATUS_CLOSED_CHAT)));            
   return erLhcoreClassChat::getCount($filterCombined, 'lh_chat', 'AVG(chat_duration)');

I believe that with subqueries I resolve, but I'm not sure

obs: only the query solves, or the idea, and I implement in the system

Grabbing all chats and checking for one-to-one time is not feasible, thinking of large amounts of existing chats

    
asked by anonymous 12.03.2018 / 02:15

1 answer

1

Yes, you can only do this with SQL. You can use the case structure to define a common value between records in each interval and then group them together by calculating the number of records in each group.

For example: 

select
  case
    when duration between 0 and 30 then '0-30s'
    when duration between 31 and 60 then '30-60s'
    else '60-120s'
  end as tempo,
  count(*) as total
from atendimento
group by tempo

See working in SQLFiddle

    
12.03.2018 / 02:38
Access resources spring image Website in VS Community 2017