Mysql sub query issues
Without the DDL and sample (raw) data, and not sure how you go from the first result set having all cnam = "foo foo" to the second result set having CNAM = "foo" or "foo2" ...

You could start with something like ...

select cnam,
       count(disposition) as TOTAL,
       sum(case when disposition = 'ANSWERED' then 1 else 0 end) as COMPLETED,
       sum(case when disposition = 'NO ANSWER' then 1 when disposition = 'BUSY' then 1 else 0 end) as MISSED,
       SEC_TO_TIME(sum(billsec)) as "Total DURATION",
       SEC_TO_TIME(avg(billsec)) as "AVG DURATION"

from   (SELECT SUBSTRING(channel,1, LOCATE("-",channel, LENGTH(channel)-8)-1) AS chan1,
               cnam,
               duration,
               disposition 

        FROM   asteriskcdrdb.cdr

        WHERE  calldate &gt;= '2017-12-08 00:00:00' 
        AND    calldate &lt;= '2017-12-08 23:59:59' 
        AND    (duration-billsec) &gt;=0
        HAVING chan1 LIKE 'SIP/3%' 
            or chan1 IN ('SIP/2000','SIP/2016','SIP/2046')
       ) dt

group by cnam
order by cnam asc


... keep in mind that I'm not 100% sure of your logic (eg, should total/avg duration be based only on COMPLETED data?) so I mostly cut-n-pasted what you had originally.



OK, thinking about this a bit more ...

The derived table isn't really doing anything other than filtering out rows from the base table, which means the derived table could be eliminated.

One possible flattening of the query (using RDFozz's modified WHERE clause):

select cnam,
       count(disposition) as TOTAL,
       sum(case when disposition = 'ANSWERED'  then 1 else 0 end) as COMPLETED,
       sum(case when disposition = 'NO ANSWER' then 1 when disposition = 'BUSY' then 1 else 0 end) as MISSED,
       SEC_TO_TIME(sum(billsec)) as "Total DURATION",
       SEC_TO_TIME(avg(billsec)) as "AVG DURATION"

FROM   asteriskcdrdb.cdr

WHERE  calldate &gt;= '2017-12-08 00:00:00' 
AND    calldate &lt;= '2017-12-08 23:59:59' 
AND    (duration-billsec) &gt;=0
AND    (   channel LIKE 'SIP/3%' 
        OR channel LIKE 'SIP/2000-%' 
        OR channel LIKE 'SIP/2016-%' 
        OR channel LIKE 'SIP/2046-%')

group by cnam
order by cnam asc