CODEWITHSUNDEEP
sqlsql-servert-sqlcumulative-sum
Jande
Jande

Reputation: 1705

Calculating running totals for partitions of data

I have table named #t1 and three columns. CODE, Column1, and Column2.

        create table #t1 (
        CODE  NVARCHAR(20),
        COLUMN1  NUMERIC(18,2),
        COLUMN2  NUMERIC(18,2) 
                              )

And i have some data:

      INSERT INTO #t1 (CODE,COLUMN1,COLUMN2)
VALUES ('432', 0,100),
       ('TOTAL FOR 432',0,100),
       ('4320001',0,250),
       ('4320001',50,0),
       ('4320001',0,140),
       ('4320001',300,0),
       ('TOTAL FOR 4320001',350,390),
       ('432002',200,0),
       ('432002',0,100),
       ('TOTAL FOR 432002',200,100)




       drop table #t1

I want to have 4 column (named BALANCE). Balance must be column that represent running totals between two columns (Column1 - Column2) for each group of data. For each group total must start from zero.

Output:

             CODE                    COLUMN1        COLUMN2        BALANCE
             432                      0.00           100.00         -100
   TOTAL     432                      0.00           100.00         -100
         4320001                      0.00           250.00         -250   
         4320001                     50.00             0.00         -200
         4320001                      0.00           140.00         -340
         4320001                    300.00             0.00          -40
   TOTAL 4320001                    350.00           390.00          -40
          432002                    200.00             0.00          200
          432002                      0.00           100.00          100
   TOTAL  432002                    200.00           100.00          100

After total 432 it starts to count again for total 4320001 and agian for total 432002..... How can i get this result?

I'm using MS SQL SERVER 2014

SQL FIDDLE

EDIT: I have tried so far this (but that is not good):

 SUM(sum(column1)-sum(column2)) OVER(ORDER BY code rows UNBOUNDED PRECEDING) AS SALDO

Upvotes: 0

Views: 174

Answers (2)

wiretext
wiretext

Reputation: 3342

as per your output :)

  DECLARE @t1 TABLE (
  CODE nvarchar(20),
  COLUMN1 numeric(18, 2),
  COLUMN2 numeric(18, 2)
)

INSERT INTO @t1 (CODE, COLUMN1, COLUMN2)
  VALUES ('432', 0, 100),
  ('TOTAL FOR 432', 0, 100),
  ('4320001', 0, 250),
  ('4320001', 50, 0),
  ('4320001', 0, 140),
  ('4320001', 300, 0),
  ('TOTAL FOR 4320001', 350, 390),
  ('432002', 200, 0),
  ('432002', 0, 100),
  ('TOTAL FOR 432002', 200, 100)

-- CTE
;
WITH CTE
AS (SELECT
  *,
  ROW_NUMBER() OVER (ORDER BY @@rowcount) RowNum -- Order as per the input
FROM @t1)
-- get from CTE
SELECT
  *,
  SUM(COLUMN1 - COLUMN2) OVER (PARTITION BY code ORDER BY RowNum) AS Balance --sum using PARTITION
FROM CTE
ORDER BY RowNum

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use the following query to get the expected result set:

SELECT CODE_NAME, COLUMN1, COLUMN2, BALANCE
FROM (
  SELECT CODE, CODE AS CODE_NAME, 1 AS ORD, COLUMN1, COLUMN2,
         SUM(COLUMN1 - COLUMN2) 
         OVER (PARTITION BY CODE ORDER BY id) AS BALANCE
  FROM t1

  UNION ALL

  SELECT CODE, CONCAT ('TOTAL FOR ', CODE) AS CODE_NAME, 2 AS ORD,
         SUM(COLUMN1), SUM(COLUMN2), 
         SUM(COLUMN1 - COLUMN2) AS BALANCE
  FROM t1
  GROUP BY CODE
)  AS t
ORDER BY CODE, ORD

The above assumes that there is an auto-increment PK column, id, that is used to define order. It also assumes that total aggregates are not stored in your original table.

Running totals are easily calculated using windowed version of SUM aggregate function. PARTITION BY in the OVER clause results in totals being calculated per CODE slice. ORDER BY id in the OVER clause is what causes running total calculation.

Total aggregates are calculated by a separate query. ORD field helps us correctly order CODE slices after UNION ALL is performed.

Demo here

Upvotes: 0

Related Questions