sql - 我如何获得总数？

我无法获得确切的查询来获取总计表的全部总数。我想在call_time表中获取每个日期的总和。这是我的查询:

SELECT call_type, channel, call_time,
       count (CASE WHEN upper(status) = upper('no answer') THEN 1 ELSE NULL END) AS cnt_no_answer,
       count (CASE WHEN upper(status) = upper('answered') THEN 1 ELSE NULL END) AS cnt_answer,
       count (status) AS cnt_total
FROM   app_account.cc_call
WHERE  channel = 'DAHDI/i1/'
AND    call_time BETWEEN ('30-DEC-2013') AND ('04-JAN-2014')
GROUP BY call_type, channel, call_time;

该查询的一些输出:

CALL_TYPE        CHANNEL    CALL_TIME CNT_NO_ANSWER  CNT_ANSWERED   CNT_TOTAL
LANDLINE         DAHDI/i1/  03-JAN-14     1            0               1
MOBILE-SUN       DAHDI/i1/  03-JAN-14     0            1               1
MOBILE-SUN       DAHDI/i1/  03-JAN-14     1            0               1
LANDLINE         DAHDI/i1/  03-JAN-14     1            0               1
LANDLINE         DAHDI/i1/  03-JAN-14     1            0               1
LANDLINE         DAHDI/i1/  03-JAN-14     1            0               1
MOBILE-SUN       DAHDI/i1/  02-JAN-14     1            0               1
MOBILE-SUN       DAHDI/i1/  02-JAN-14     0            1               1
LANDLINE         DAHDI/i1/  02-JAN-14     0            1               1
LANDLINE         DAHDI/i1/  02-JAN-14     1            0               1
MOBILE-SMART     DAHDI/i1/  02-JAN-14     1            0               1

我的例外输出:

CALL_TIME  CNT_NO_ANSWER CNT_ANSWERED
03-JAN-14   27             10
02-JAN-14   48             20

请帮我。

谢谢!

最佳答案

试试这个:

SELECT CALL_TYPE, CHANNEL, TRUNC(CALL_TIME)
      ,COUNT (CASE WHEN UPPER(STATUS) = UPPER('no answer') THEN 1 END) AS CNT_NO_ANSWER
      ,COUNT (CASE WHEN UPPER(STATUS) = UPPER('answered') THEN 1 END) AS CNT_ANSWER
      ,COUNT (STATUS) AS CNT_TOTAL
FROM APP_ACCOUNT.CC_CALL
WHERE CHANNEL = 'DAHDI/i1/'
AND CALL_TIME BETWEEN ('30-DEC-2013') AND ('04-JAN-2014')
GROUP BY CALL_TYPE, CHANNEL, TRUNC(CALL_TIME);

如果CALL_TIME包含时间值，并且您想对每个日期GROUP BY进行编码，则应使用trunc将CALL_TIME编码为其日期。

关于sql - 我如何获得总数？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20962674/