我无法获得确切的查询来获取总计表的全部总数。我想在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/