Stephen Stephen - 1 month ago 5
MySQL Question

Display different values from same data set in one query

I have a query that shows our abandonment rate, however we want to see how many of these abandonments were abandoned after 6 seconds of ringing.

Currently I get my results as:

count | queue | waittime


I want it displayed as:

count | count6 | queue | waittime


The query is:

SELECT count(*) AS count,queue,sum(info3) as waittime FROM queue_stats
LEFT JOIN qevent ON qevent=qevent.event_id
LEFT JOIN qname ON qname=qname.queue_id
WHERE datetime>=CURDATE() AND event IN ('ABANDON','EXITWITHKEY','EXITWITHTIMEOUT','EXITEMPTY')
GROUP BY qname
ORDER BY null


To get the value of calls abandoned over 6 seconds, I add
AND info3 > 6
just before GROUP BY.

Any suggestions how I can have it in one query?

Answer

Use conditional counting - include the condition within the count() (or sum()) function:

SELECT count(*) AS count,
       count(if(info3 > 6, 1, null)) as six_count,
       queue,
       sum(info3) as waittime
FROM queue_stats
LEFT JOIN qevent ON qevent=qevent.event_id
LEFT JOIN qname ON qname=qname.queue_id
WHERE datetime>=CURDATE() AND event IN ('ABANDON','EXITWITHKEY','EXITWITHTIMEOUT','EXITEMPTY') 
GROUP BY qname 
ORDER BY null

You can use a case expression in place of the if() function as well.