Разбить по дням количество результатов

Question

Необходимо разбить по дням следующую выборку
   (select  count(*) c from cdrquery t, calend t2 where trunc(to_date(t.data_start_talk1,'YYYY-MM-DD HH24:MI:SS'))=t2.num_day and t.redirect_num1='8800'
   and trunc(to_date(t.data_start_talk1,'YYYY-MM-DD-HH24:MI:SS')) between '01.06.2020' and '30.06.2020'
   and substr(t.data_start_talk1,12,100) between '00:00:00' and '08:59:59'
   and t2.weekend=0 and t.type_output='user-service')

Чтобы получалось примерно так
  01.06.2020  5

02.06.2020  10

...

29.06.2020  0

30.06.2020  15

Alexandr · Answer

Необходимо добавить в запрос GROUP BY:
SELECT
    trunc(TO_DATE(t.data_start_talk1, 'YYYY-MM-DD HH24:MI:SS')) d,
    COUNT(*) c
FROM
    cdrquery   t,
    calend     t2
WHERE
    trunc(TO_DATE(t.data_start_talk1, 'YYYY-MM-DD HH24:MI:SS')) = t2.num_day
    AND t.redirect_num1 = '8800'
    AND trunc(TO_DATE(t.data_start_talk1, 'YYYY-MM-DD-HH24:MI:SS')) BETWEEN '01.06.2020' AND '30.06.2020'
    AND substr(t.data_start_talk1, 12, 100) BETWEEN '00:00:00' AND '08:59:59'
    AND t2.weekend = 0
    AND t.type_output = 'user-service'
GROUP BY
    trunc(TO_DATE(t.data_start_talk1, 'YYYY-MM-DD HH24:MI:SS'))
ORDER BY
    trunc(TO_DATE(t.data_start_talk1, 'YYYY-MM-DD HH24:MI:SS'))

Разбить по дням количество результатов

One Answer

Add your own answers!

Ask a Question