CODEWITHSUNDEEP
oracle-databasecumulative-sum
rookie17768
rookie17768

Reputation: 61

how to do cumulative sums in oracle

I am new to SQL and I wanted to do 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      22
2019-08-02  19      5       3       27      49
2019-08-03  23      6       6       35      84
2019-08-04  7       9       13      29      113
2019-08-05  4       17      2       23      136
2019-08-06  10      5       16      31      167
2019-08-07  3       12      11      26      193

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 :(

Upvotes: 3

Views: 14723

Answers (2)

Irakli dd
Irakli dd

Reputation: 56

SELECT t.user_id,
       t.transactions_,
       SUM(t.transactions_) over(ORDER BY t.user_id) cum_sum
FROM FEBRUARY_2023_USER_ACTIVITIES t

enter image description here

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

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
...

Upvotes: 4

Related Questions