Cumulative sums

Question

I want a report which shows the daily number of tickets per shift and also the to-date total.

Here's the query I have which shows the first 5 columns below:

SELECT 
    TO_CHAR(DTTM,'YYYY-MM-DD') as "DATE"
    , COUNT(CASE WHEN TO_CHAR(DTTM, 'HH24:MI') BETWEEN '14:00' AND '22:00' THEN TKTNUM ELSE NULL END) AS "DAYS"
    , COUNT(CASE WHEN TO_CHAR(DTTM, 'HH24:MI') BETWEEN '06:00' AND '14:00' THEN TKTNUM ELSE NULL END) AS "MIDS"
    , COUNT(CASE WHEN TO_CHAR(DTTM, 'HH24:MI') NOT BETWEEN '06:00' AND '22:00' THEN TKTNUM ELSE NULL END) AS "SWINGS"
    , COUNT(TKTNUM) AS "TOTAL"
FROM TKTHISTORY
GROUP BY TO_CHAR(DTTM,'YYYY-MM-DD')
ORDER BY TO_CHAR(DTTM,'YYYY-MM-DD')

DATE	DAYS	MIDS	SWINGS	TOTAL
2019-08-01	8	13	1	22
2019-08-02	19	5	3	27
2019-08-03	23	6	6	35
2019-08-04	7	9	13	29
2019-08-05	4	17	2	23
2019-08-06	10	5	16	31
2019-08-07	3	12	11	26

The 6th column should be the cumulative sum for the dates. I tried browsing the internet and read about "over" and "partition by" but I still can't figure out how to use it,

Answer 1

Here's an example based on Scott's EMP table, which counts jobs per department. The last column is the "running total" value.

Sample data shows that there are 3 employees in DEPTNO = 10, 5 of them in dept. 20 and 6 in dept. 30:

SQL> select deptno, empno, ename from emp order by deptno;

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        10       7782 CLARK
        10       7839 KING
        10       7934 MILLER
        20       7566 JONES
        20       7902 FORD
        20       7876 ADAMS
        20       7369 SMITH
        20       7788 SCOTT
        30       7521 WARD
        30       7844 TURNER
        30       7499 ALLEN
        30       7900 JAMES
        30       7698 BLAKE
        30       7654 MARTIN

14 rows selected.

Query then looks like this:

SQL> select
  2    deptno,
  3    count(empno) emps_per_dept,
  4    sum(count(*)) over (order by deptno) total
  5  from emp
  6  group by deptno;

    DEPTNO EMPS_PER_DEPT      TOTAL
---------- ------------- ----------
        10             3          3
        20             5          8
        30             6         14

SQL>

---

Which, in your case, might be like this:

SELECT 
   ...
  ,sum(COUNT(TKTNUM)) over (order by TO_CHAR(DTTM,'YYYY-MM-DD')) AS "TOTAL"
FROM TKTHISTORY
...