SQLlite strftime 函数按月获取分组数据 [英] SQLlite strftime function to get grouped data by months

问题描述

我有以下结构和数据的表:

i have table with following structure and data:

例如，我想在给定日期范围内按月分组数据(从 2014-01-01 到 2014-12-31).无法获得某些月份的数据，但我仍然需要在结果信息中提供给定月份的结果 0.

I would like to get grouped data by months in given date range for example (from 2014-01-01 to 2014-12-31). Data for some months cannot be available but i still need to have in result information that in given month is result 0.

结果应具有以下格式:

    MONTH   |    DIALS_CNT   | APPT_CNT | CONVERS_CNT | CANNOT_REACH_CNT | 
    2014-01 |    100         | 50       | 20          |  30              |
    2014-02 |    100         | 40       | 30          |  30              |
    2014-03 |      0         |  0       |  0          |   0              |
etc..

哪里

    APPT_CNT = WHERE call.result = APPT
    CONVERS_CNT = WHERE call.result = CONV_NO_APPT
    CANNOT_REACH_CNT = WHERE call.result = CANNOT_REACH

我该如何使用strftime 功能来实现?

How can i do it please with usage function strftime ?

非常感谢任何帮助或示例.

Many thanks for any help or example.

推荐答案

SELECT Month,
       (SELECT COUNT(*)
        FROM MyTable
        WHERE date LIKE Month || '%'
       ) AS Dials_Cnt,
       (SELECT SUM(Call_Result = 'APPT')
        FROM MyTable
        WHERE date LIKE Month || '%'
       ) AS Appt_Cnt,
       ...
FROM (SELECT '2014-01' AS Month UNION ALL
      SELECT '2014-02'          UNION ALL
      SELECT '2014-03'          UNION ALL
      ...
      SELECT '2014-12')

这篇关于SQLlite strftime 函数按月获取分组数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！