SQLize Online / PHPize Online  /  SQLtest Online

WITH t (card, start_date) AS ( SELECT 1, TO_DATE('26.12.2023 10:00', 'dd.mm.yyyy hh24:mi') FROM DUAL UNION ALL SELECT 2, TO_DATE('01.11.2023 08:53', 'dd.mm.yyyy hh24:mi') FROM DUAL UNION ALL SELECT 3, TO_DATE('01.11.2023 10:30', 'dd.mm.yyyy hh24:mi') FROM DUAL UNION ALL SELECT 4, TO_DATE('15.11.2023 23:59', 'dd.mm.yyyy hh24:mi') FROM DUAL ), t2(dt, cnt) AS ( SELECT TRUNC(t.start_date) dt, COUNT(t.card) cnt FROM t WHERE t.start_date >= TRUNC(ADD_MONTHS(sysdate, -1), 'mm') AND t.start_date < TRUNC(sysdate, 'mm') GROUP BY TRUNC(t.start_date) UNION ALL SELECT TRUNC(ADD_MONTHS(sysdate, -1), 'mm') + LEVEL - 1 dt, 0 cnt FROM dual CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate, -1)), 'dd')) ) SELECT t2.dt, SUM(t2.cnt) cnt FROM t2 GROUP BY t2.dt ORDER BY t2.dt;