Running Total by Grouped Records in Table

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 for grouped data SQL

You are mixing window functions and aggregation. The logic looks like:

SUM(SUM(CASE WHEN CostCenter IN ('9000' '2100', '2200', '2300')
             THEN Amount
        END)
   ) OVER (ORDER BY LastDayOfMonth) AS Invoiced,
SUM(SUM(CASE WHEN CostCenter > '9000' and CostCenter < '9999'
             THEN Amount
        END)
   ) OVER (ORDER BY LastDayOfMonth) AS RunningTotalCosts

That is, the SUM()s need to nested. The inner SUM() is the aggregation. The outer SUM() is the window function.

Running total per group in SQL Server 2008

The best way to do this is with cumulative sum. Perhaps it is time to update to a more recent version of SQL Server?

In SQL Server 2008, you can use apply or a correlated subquery:

Select [Group], [Day], sum(money) as [Money],
       (select sum(t2.money)
        from table_name t2
        where t.group = t2.group and t2.day <= t.day
       ) as Column4
From Table_Name t
Group by [Group], [Day];

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

SQL Running Total Grouped By Limit

You can use CTE to achieve the goal.

If one of the item exceeds Qty 50, a group still assign for it

DECLARE @Data TABLE (ID int identity(1,1) primary key, Qty int)
INSERT @Data VALUES (10), (20), (30), (40), (2), (3), (10), (25), (15), (5)

;WITH cte AS
(
    SELECT ID, Qty, 1 AS [Group], Qty AS RunningTotal FROM @Data WHERE ID = 1
    UNION ALL
    SELECT data.ID, data.Qty, 
        -- The group limits to 50 Qty
        CASE WHEN cte.RunningTotal + data.Qty > 50 THEN cte.[Group] + 1 ELSE cte.[Group] END, 
        -- Reset the running total for each new group
        data.Qty + CASE WHEN cte.RunningTotal + data.Qty > 50 THEN 0 ELSE cte.RunningTotal END
    FROM @Data data INNER JOIN cte ON data.ID = cte.ID + 1
)
SELECT ID, Qty, [Group] FROM cte

SQL - running total when data already grouped

The ANSI standard way of doing a cumulative sum is:

select t.*, sum(totalpmtamt) over (order by mdate) as runningsum
from #testdata t
order by t.mdate;

Not all databases support this functionality.

If your database doesn't support that functionality, I would go for a correlated subquery:

select t.*,
       (select sum(t2.totalpmtamt)
        from #testdata t2
        where t2.mdate <= t.mdate
       ) as runningsum
from #testdata
order by t.mdate;

Running total with group by in MySql

Try using the result-set of group by operation in a Derived Table, and then compute the running balance.

Also, note that I have done proper Aliasing for code clarity (readability), and avoid ambiguous operations.

Try the following:

SELECT dt.*, 
       @running_total := @running_total + dt.total AS cumulative_sum 
FROM (
      SELECT t1.*, 
             SUM(t1.payment_sum) as total 
      FROM payments AS t1 
      WHERE t1.id = ? 
      GROUP BY t1.source, t1.report_date 
     ) AS dt 
CROSS JOIN (SELECT @running_total := 0) AS t2 

MySQL Running Total on multiple columns grouped by another column

Your user defined variable gets overwritten in the first column. Add a second one for lost

CREATE TABLE games (
  `TeamID` INTEGER,
  `GameID` INTEGER,
  `w` INTEGER,
  `l` INTEGER
);

INSERT INTO games
  (`TeamID`, `GameID`, `w`, `l`)
VALUES
  ('1', '1', '1', '0'),
  ('1', '2', '0', '1'),
  ('1', '3', '1', '0'),
  ('1', '9', '0', '1'),
  ('1', '10', '1', '0'),
  ('2', '4', '1', '0'),
  ('2', '5', '1', '0'),
  ('2', '6', '1', '0'),
  ('2', '7', '0', '1'),
  ('2', '8', '0', '1'),
  ('3', '1', '0', '1'),
  ('3', '2', '1', '0'),
  ('3', '3', '0', '1'),
  ('3', '7', '1', '0'),
  ('3', '8', '1', '0'),
  ('4', '4', '0', '1'),
  ('4', '5', '0', '1'),
  ('4', '6', '0', '1'),
  ('4', '9', '1', '0'),
  ('4', '10', '0', '1');

    select g.*,
       (@w := if(@TeamID = TeamID, @w + w, 
                 if(@TeamID := TeamID, w,w)
                )
       ) as cum_w, 
       (@l := if(@Team2ID = TeamID, @l + l,
                 if(@Team2ID := TeamID, l, l)
                )
       ) as cum_l        
from (select TeamID, GameID, w, l
      from games order by TeamID, GameID) g
cross join (select @TeamID := -1, @Team2ID := -1, @w := 0, @l := 0) params
;

TeamID | GameID |  w |  l | cum_w | cum_l
-----: | -----: | -: | -: | ----: | ----:
     1 |      1 |  1 |  0 |     1 |     0
     1 |      2 |  0 |  1 |     1 |     1
     1 |      3 |  1 |  0 |     2 |     1
     1 |      9 |  0 |  1 |     2 |     2
     1 |     10 |  1 |  0 |     3 |     2
     2 |      4 |  1 |  0 |     1 |     0
     2 |      5 |  1 |  0 |     2 |     0
     2 |      6 |  1 |  0 |     3 |     0
     2 |      7 |  0 |  1 |     3 |     1
     2 |      8 |  0 |  1 |     3 |     2
     3 |      1 |  0 |  1 |     0 |     1
     3 |      2 |  1 |  0 |     1 |     1
     3 |      3 |  0 |  1 |     1 |     2
     3 |      7 |  1 |  0 |     2 |     2
     3 |      8 |  1 |  0 |     3 |     2
     4 |      4 |  0 |  1 |     0 |     1
     4 |      5 |  0 |  1 |     0 |     2
     4 |      6 |  0 |  1 |     0 |     3
     4 |      9 |  1 |  0 |     1 |     3
     4 |     10 |  0 |  1 |     1 |     4

db<>fiddle here

---

Related Topics