Average mysql with multiple conditions

Question

I am mounting a graph containing the amount of CHATS that were answered in a given time, example:

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 basic structure of my table is:

ID | Title | Chat duration

1 | Test | 20

2 | Teste2 | 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 with Queries it solves, but I’m not sure

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

Take all the chats and do the one-to-one check of the duration I believe is not feasible, thinking of large amounts of existing chats

Answer 1

Yes, you can do this only with SQL. You can use the structure marry to define a common value between the records of each interval and, later, to group them together, calculating the amount 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 on Sqlfiddle}