Running Total by Group SQL (Oracle)

Running Total by Group SQL (Oracle)

Ah, I think I have figured it out.

select a.*, sum(Amount) over (partition by Location, Product order by Date) as Running_Amt
from Example_Table a

Oracle SQL - Running Sum based on group by and condition

You could make a first window sum() to define the groups, then use it as partition for the outer query:

select 
    t.*,
    sum(count1) over(partition by id, grp order by seq) running_count
from (
    select 
        t.*,
        sum(label) over(partition by id order by seq desc) grp
    from mytable t
) t

Demo on DB Fiddle:

ID | SEQ | LABEL | COUNT1 | GRP | RUNNING_COUNT
-: | --: | ----: | -----: | --: | ------------:
 1 |   1 |     0 |      3 |   1 |             3
 1 |   2 |     0 |      2 |   1 |             5
 1 |   3 |     0 |      6 |   1 |            11
 1 |   4 |     1 |      2 |   1 |            13
 1 |   5 |     0 |      3 |   0 |             3
 1 |   6 |     0 |      5 |   0 |             8
 2 |   1 |     0 |      2 |   1 |             2
 2 |   2 |     1 |      1 |   1 |             3
 2 |   3 |     0 |      3 |   0 |             3

How to get running total in Oracle using SUM and OVER

By default analytic functions use a RANGE windowing directive (see below - the default is the first version). What you are looking for is a ROWS directive (see below), which is not the default so you must include it explicitly.

The RANGE windowing clause (default) does exactly what you noticed: it treats all "tied" rows as "included in the sum".

ADDED: Link to documentation https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#i97640

with test_data ( x ) as (
       select  10 from dual union all
       select  20 from dual union all
       select  30 from dual union all
       select  30 from dual union all
       select  30 from dual union all
       select 100 from dual
     )
select x
     , sum(x) over (order by x range between unbounded preceding and current row) range_sum
     , sum(x) over (order by x rows  between unbounded preceding and current row) rows_sum
from   test_data
order by x;

         X  RANGE_SUM   ROWS_SUM
---------- ---------- ----------
        10         10         10
        20         30         30
        30        120         60
        30        120         90
        30        120        120
       100        220        220

Oracle SQL - trying to calculate running total with Group By without having an existing numerical column to sum

If a given product is never ordered twice on the same data, you can just use a window count:

select
    t.*,
    count(*) over(partition by reference order by date_ordered) running_count
from mytable t

If there are duplicates, then you need aggregation:

select  
    reference,
    date_ordered,
    sum(count(*)) over(partition by reference order by date_ordered) running_count
from mytable
group by reference, date_ordered

Finally: if you want to generate all combinations of dates and product, with the associated runnig count, then you would do:

select
    r.reference,
    d.date_ordered,
    sum(count(t.reference)) over(partition by r.reference order by d.date_ordered) running_count
from (select distinct date_ordered from mytable) d
cross join (select distinct reference from mytable) r
left join mytable t 
    on t.date_ordered = d.date_ordered and t.reference = r.reference
group by d.date_ordered, r.reference

Running total by grouped records in table

Do you really need the extra table?

You can get that data you need with a simple query, which you can obviously create as a view if you want it to appear like a table.

This will get you the data you are looking for:

select 
    account, bookdate, amount, 
    sum(amount) over (partition by account order by bookdate) running_total
from t
/

This will create a view to show you the data as if it were a table:

create or replace view t2
as
select 
    account, bookdate, amount, 
    sum(amount) over (partition by account order by bookdate) running_total 
from t
/

If you really need the table, do you mean that you need it constantly updated? or just a one off? Obviously if it's a one off you can just "create table as select" using the above query.

Test data I used is:

create table t(account number, bookdate date, amount number);

insert into t(account, bookdate, amount) values (1, to_date('20080101', 'yyyymmdd'), 100);

insert into t(account, bookdate, amount) values (1, to_date('20080102', 'yyyymmdd'), 101);

insert into t(account, bookdate, amount) values (1, to_date('20080103', 'yyyymmdd'), -200);

insert into t(account, bookdate, amount) values (2, to_date('20080102', 'yyyymmdd'), 200);

commit;

edit:

forgot to add; you specified that you wanted the table to be ordered - this doesn't really make sense, and makes me think that you really mean that you wanted the query/view - ordering is a result of the query you execute, not something that's inherant in the table (ignoring Index Organised Tables and the like).

Running total count with group by; SQL

Use window functions:

SELECT EXTRACT(year FROM HIRE_DATE) "Year", COUNT(*),
       SUM(COUNT(*)) OVER (ORDER BY MIN(HIRE_DATE)) as running_count
FROM hr.Employees
GROUP BY EXTRACT(year FROM HIRE_DATE)
ORDER BY "Year" DESC;

Running total with subgroups

You're close, but your current query is doing the SUM over all rows in each group. You just need to add a row specification for your cumulative sum:

Sum(sales-returns) over(
  partition by Version, Country 
  Order by Week 
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- all rows including current row
) Running_stock

TD Manual

Running total over repeating group by items based on time in Oracle SQL

In order to get the sums you are looking for you need a way to group the values you are interested in. You can generate a grouping ID by using the a couple of ROW_NUMBER analytic functions, one partitioned by the key value. However due to your need to duplicate the KEY column values this will need to be done in a couple of stages:

WITH t1 AS (
  SELECT dta.*
       , last_value(KEY IGNORE NULLS)          -- Fill in the missing
               OVER (ORDER BY TIME ASC) key2   -- key values
    FROM your_data dta
), t2 AS (
  SELECT t1.*
       , row_number() OVER (ORDER BY TIME)     -- Generate a
       - row_number() OVER (PARTITION BY key2  -- grouping ID
                                ORDER BY TIME) gp
    FROM t1
)
SELECT t2.*
     , sum(amt) OVER (PARTITION BY gp, key2
                          ORDER BY TIME) running_sums
  FROM t2;

The above query creates a running sum of AMT that restarts every time the key value changes. Whereas the following query used in place of the last select statement above gives the requested results, which I wouldn't term a running sum.

SELECT key2
     , MIN(TIME) start_time
     , MAX(TIME) stop_time
     , sum(amt) amt
  FROM t2
 GROUP BY key2, gp;

To see the full time values you may want to either alter your session NLS_DATE_FORMAT as below:

ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-RRRR HH24:MI:SS';

Or wrap each date column in a TO_CHAR function for output purposes.

running sum on group by

In Oracle, a running sum is easily done with the sum() ... over() window function:

select  name
,       other_name
,       name_count
,       sum(name_count) over(
            order by name, other_name) as running
from    (
        select  name
        ,       other_name
        ,       count(name) as name_count
        from    ethnicity
        group by 
                name
        ,       other_name
        order by
                name
        ,       other_name
        ) subqueryalias

Example at SQL Fiddle

---

Related Topics