Stephen Stephen - 16 days ago 5
MySQL Question

Change layout of SELECT result

I have the below query which I want to change the way the results are displayed. I looked into using pivots, but I can't grasp how I make it work and would appreciate a helping hand.

The (shortened) query:

select
# total counts
count(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104) and data1 <= 5,1, null)) as under_5_sec,
count(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104) and data1 <= 10,1, null)) as under_10_sec,

# percentage answered of calls within timeframe
count(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104) and data1 <= 5,1, null)) / COUNT(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104, 9106, 9108, 9600, 9602, 9500, 9502, 9800, 9802),1, null)) * 100 as under_5_perct,
count(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104) and data1 <= 10,1, null)) / COUNT(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104, 9106, 9108, 9600, 9602, 9500, 9502, 9800, 9802),1, null)) * 100 as under_10_perct,

# percentage answered of offered calls
count(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104) and data1 <= 5,1, null)) / COUNT(if(verb in ('ENTERQUEUE') and queue in (9100, 9102, 9104, 9106, 9108, 9600, 9602, 9500, 9502, 9800, 9802),1, null)) * 100 as under_5_offer,
count(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104) and data1 <= 10,1, null)) / COUNT(if(verb in ('ENTERQUEUE') and queue in (9100, 9102, 9104, 9106, 9108, 9600, 9602, 9500, 9502, 9800, 9802),1, null)) * 100 as under_10_offer

from queue_log

where partition = 'P001'
and time_id >= unix_timestamp('2016-10-30') and time_id < unix_timestamp('2016-10-31')


The results are displayed as:

under_5_sec | under_10_sec | under_5_perct | under_10_perct | under_5_offer | under_10_offer
346 | 353 | 91.7772 | 93.6340 | 87.3737 | 89.1414


I want to display the data as:

descr | per_sec | percent | offered
under_5_sec | 346 | 91.7772 | 87.3737
under_10_sec| 353 | 93.6340 | 89.1414


Any suggestions how I can achieve this?

Answer
select 
 'under_5_sec' as descr,
 count(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104) and data1 <= 5,1, null)) as per_sec,
 count(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104) and data1 <= 5,1, null)) / COUNT(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104, 9106, 9108, 9600, 9602, 9500, 9502, 9800, 9802),1, null)) * 100 as perct,
 count(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104) and data1 <= 5,1, null)) / COUNT(if(verb in ('ENTERQUEUE') and queue in (9100, 9102, 9104, 9106, 9108, 9600, 9602, 9500, 9502, 9800, 9802),1, null)) * 100 as offer,
from queue_log
where partition = 'P001'
and time_id >= unix_timestamp('2016-10-30') and time_id < unix_timestamp('2016-10-31')
UNION
select 
 'under_10_sec' as descr,
 count(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104) and data1 <= 10,1, null)) as per_sec,
 count(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104) and data1 <= 10,1, null)) / COUNT(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104, 9106, 9108, 9600, 9602, 9500, 9502, 9800, 9802),1, null)) * 100 as perct,
 count(if(verb in ('COMPLETEAGENT','COMPLETECALLER') and queue in (9100, 9102, 9104) and data1 <= 10,1, null)) / COUNT(if(verb in ('ENTERQUEUE') and queue in (9100, 9102, 9104, 9106, 9108, 9600, 9602, 9500, 9502, 9800, 9802),1, null)) * 100 as offer
from queue_log
where partition = 'P001'
and time_id >= unix_timestamp('2016-10-30') and time_id < unix_timestamp('2016-10-31')
Comments