mercredi 4 mars 2015

SQLite How to Group results by hours?

I have following SQL command which returns count of some values.



SELECT COUNT(*) AS DIALS_CNT,
SUM(CASE WHEN dc.call_result = 'APPT' THEN 1 ELSE 0 END) AS 'APPT_CNT', SUM(CASE WHEN dc.call_result = 'CONV_NO_APPT' THEN 1 ELSE 0 END) AS 'CONVERS_CNT' ,
SUM(CASE WHEN dc.call_result = 'CANNOT_REACH' THEN 1 ELSE 0 END) AS 'CANNOT_REACH_CNT' FROM dialed_calls dc ;


I would like to get results grouped by hours to get result like this



--------------------------------------------------
HOUR | APPT_CNT | CONV_NO_APPT | CANNOT_REACH_CNT
--------------------------------------------------
8:00 | 10 | 20 | 30
--------------------------------------------------
9:00 | 20 | 10 | 10
--------------------------------------------------


Etc..to get values for hours where are values higher than zero.


Structure of the table is below: enter image description here


I suppose that simple usage of the group and having will not be good solution.?


Many thanks for any advice.


Aucun commentaire:

Enregistrer un commentaire