Mysql sub query issues I have a table where I record a number of values:


channel, cnam (both varchar)
calldate (date/time)
billsec (integer)
disposition (varchar - either 'ANSWERED' or 'NO ANSWER' or 'BUSY')


I need to report on the total number of records and billsec (which is a number of seconds) for certain cnams, along with a breakdown of billsec by disposition, and the average duration.

I have a query that lets me select the correct set of rows to be reported on:

SELECT SUBSTRING(channel,1, LOCATE("-",channel, LENGTH(channel)-8)-1) AS 
chan1,cnam,billsec,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')
ORDER BY chan1 asc


This returns results like the following:

chanl     | cnam     | billsec  | disposition
----------+----------+----------+----------------
SIP/2000  | foo      |    180   |  ANSWERED
SIP/2000  | foo      |     30   |  NO ANSWER
SIP/2000  | foo      |     60   |  ANSWERED
SIP/3010  | foo foo  |     2    |  BUSY
SIP/3010  | foo foo  |     3    |  BUSY
SIP/3010  | foo foo  |    240   |  ANSWERED
SIP/3020  | foo2     |     30   |  ANSWERED
SIP/3020  | foo2     |    180   |  ANSWERED
SIP/3020  | foo2     |     10   |  NO ANSWER
...


I have the start for how the query for my final results would go:

SELECT cnam,
       sum(disposition) as TOTAL,
       sum(disposition where disposition = 'ANSWERED') as COMPLETED,
       sum(disposition where disposition = 'NO ANSWER' OR case 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'


My final results should look like this:

CNAM    | TOTAL | COMPLETED | MISSED | Total DURATION | AVG DURATION
--------+-------+-----------+--------+----------------+---------------
foo     |    3  |         3 |      1 |           1:30 |         0:32
foo foo |    3  |         3 |      2 |          03:05 |         1:02
foo2    |    3  |         2 |      1 |          03:34 |         1:12


I even know I can use a subquery to combine all this together. What I can't quite figure out is how to do that, given all the stuff that's in my initial query.
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