Can I get average of a column in mySQL DB based upon value of other column in one query?

user_7180741 0
Viewed 140

I have a table of phone call activity for a client. In the table, I have one column for the length of the call (in seconds), and another column for "first time call" (true / false). I was hoping to find a way to get the average call length of first time calls separated from the average time of non first time calls? Is this doable in a singe mySQL query?

SELECT location,
    count(*) AS total,
    sum(case when firstCall = 'true' then 1 else 0 end) AS firstCall,
    sum(case when answered = 'Yes' then 1 else 0 end) AS answered,
    sum(case when tags like '%Lead%' then 1 else 0 end) as lead,
    sum(case when tags like '%arbage%' then 1 else 0 end) as garbage,    
    avg(case when duration........firstTime = True???)
FROM staging
GROUP BY location
pivot count mysql average sql
2 Answers
SELECT location,
count(*) AS total,
sum(case when firstCall = 'true' then 1 else 0 end) AS firstCall,
sum(case when answered = 'Yes' then 1 else 0 end) AS answered,
sum(case when tags like '%Lead%' then 1 else 0 end) as lead,
sum(case when tags like '%arbage%' then 1 else 0 end) as garbage,    
sum(case when firstCall='true' then duration else 0 end)/sum(case when firstCall =     'true' then 1 else 0 end) as first_call_true_average,
sum(case when firstCall='false' then duration else 0 end)/sum(case when firstCall  = 'false' then 1 else 0 end) as first_call_false_average

 FROM staging
 GROUP BY location
user_146798850

I would phrase this as:

select 
    location,
    count(*) as total,
    sum(firstcall = 'true'  ) as cnt_firstcall,
    sum(answered = 'Yes'    ) as cnt_answered,
    sum(tags like '%Lead%'  ) as cnt_lead,
    sum(tags like '%arbage%') as cnt_garbage,    
    avg(case when firstcall = 'true'  then duration end) as avg_first_call,_duration
    avg(case when firstcall = 'false' then duration end) as avg_non_first_call_duration
from staging
group by location

Rationale:

  • MySQL interpret true/false conditions as 1/0 values in numeric context, which greatly shortens the conditional sum()s

  • avg() ignores null values, so a simple case expression is sufficient to compute the conditional averages

user_106767160
Related