Mysql sub query issues

MySQLsubquery

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 >= '2017-12-08 00:00:00' AND calldate <= '2017-12-08 
23:59:59' AND (duration-billsec) >=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.

Best Answer

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 >= '2017-12-08 00:00:00' 
        AND    calldate <= '2017-12-08 23:59:59' 
        AND    (duration-billsec) >=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 >= '2017-12-08 00:00:00' 
AND    calldate <= '2017-12-08 23:59:59' 
AND    (duration-billsec) >=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

Related Solutions

Mysql sub query returns more than 1 row

        (SELECT courses  FROM wp_category WHERE CatID =401) 
        OR 
        (SELECT  meta_value FROM wp_postmeta WHERE  post_id IN (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' )

This is a condition, but you need n values.

This should work:

(
    ID IN 
        (
            (SELECT courses  FROM wp_category WHERE CatID =401) 
        )
    OR
    ID IN 
        (
            (SELECT  meta_value FROM wp_postmeta WHERE  post_id IN 
                (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' )
        )
)

Also take care about subqueries with MySQL. In your case a sub- subquery.

Take a look here: https://stackoverflow.com/questions/12356784/mysql-dependent-sub-query-with-not-in-in-the-where-clause-is-very-slow

Ms-access – Alternative to sub query in MS Access

As @Max Vernon suggested, you can create sub queries in MS Access, so perhaps I jumped the gun on that one! I couldn't understand why my sub query wasn't working; I still don't understand fully Why this solution works but the other one didn't.

I followed these steps:

Create sql subquery as a saved query in Access. Test. - Success
In a new query, added the saved query, added my Modification_Log table. Test. - Success
Added the Employee table, linked this. Test. - Success
Added my query criteria. Test. - Success
I finally replaced my reference to the saved query with my sub query. Test. - Success

What I end up with is this:

SELECT Employee.EmployeeID, Employee.PositionID, Employee.Title, Employee.Firstname, Employee.Surname, Employee.Email, Modification_Log.ModifiedDate, Modification_Log.ModifiedEmployee
FROM 
         (SELECT Modification_Log.RecordID, Max(Modification_Log.ModificationID) As NewestModificationID
         FROM Modification_Log
         GROUP BY Modification_Log.RecordID) As LogMax 
INNER JOIN (Employee INNER JOIN Modification_Log ON Employee.EmployeeID = Modification_Log.RecordID) ON (LogMax.NewestModificationID = Modification_Log.ModificationID) AND (LogMax.RecordID = Employee.EmployeeID)
WHERE Employee.EmployeeID=3 AND Modification_Log.TableName='Employee';

Which is almost identical apart from the fact the sub query immediately follows the FROM clause, I didn't think the order of joins would make a difference but this is MS Access we're talking about.

Best Answer

Related Solutions

Mysql sub query returns more than 1 row

Ms-access – Alternative to sub query in MS Access

Related Question