1

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?

Stephen
  • 149
  • 3
  • 12
  • Possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) – CLAbeel Nov 16 '16 at 16:51

1 Answers1

1
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')
Ashokkumar M. Prajapati
  • 483
  • 4
  • 12