I need a new line with the sum of the previous lines

Question

I want all table rows and whenever the value in the bank field changes to a new row with a subtotal of the fields: value1, value2, value3

I want to do the following with an SQL query:

Create Table #MyTable (
    bank nvarchar(255),
    account nvarchar(255),
    value1 decimal(18,2),
    value2 decimal(18,2),
    value3 decimal(18,2),
);

INSERT INTO #MyTable
  (bank, account, value1, value2, value3)
VALUES
  ('VR-Bank', 'Kto-A', 1, 2, 3),
  ('VR-Bank', 'Kto-B', 3, 2, 1),
  ('VR-Bank', 'Kto-C', 4, 4, 5),
  ('NOSPA', 'Kto-X', 1, 1, 1),
  ('NOSPA', 'Kto-Y', 4, 3, 2);

I would like the sum without previous summation because these values already exist

Select 
    bank,
    account,
    SUM(value1), -- I would like this without SUM()
    SUM(value2),
    SUM(value3)
from #MyTable
GROUP BY 
    GROUPING SETS ((bank), (account)) -- Without summing up this does not work

-- NULL Kto-A   1.00    2.00    3.00
-- NULL Kto-B   3.00    2.00    1.00
-- NULL Kto-C   4.00    4.00    5.00
-- NULL Kto-X   1.00    1.00    1.00
-- NULL Kto-Y   4.00    3.00    2.00
-- NOSPA    NULL    5.00    4.00    3.00
-- VR-Bank  NULL    8.00    8.00    9.00

I would like to achieve the following:

-- ======================================

-- VR-Bank  Kto-A   1.00    2.00    3.00 -- here also the value for the bank
-- VR-Bank  Kto-B   3.00    2.00    1.00
-- VR-Bank  Kto-C   4.00    4.00    5.00
-- VR-Bank  NULL    8.00    8.00    9.00 -- here the SUM
-- NOSPA    Kto-X   1.00    1.00    1.00
-- NOSPA    Kto-Y   4.00    3.00    2.00
-- NOSPA    NULL    5.00    4.00    3.00 -- SUM for the next bank

Drop Table #MyTable;

I hope someone can help me.

Possible duplicate of [SQL SERVER T-SQL Calculate SubTotal and Total by group](https://stackoverflow.com/q/32786518/11683) — GSerg, Apr 27 '18 at 09:52
Thank you very much, that fits. I tried GROUP BY, ROLLUP, CUBE and more before. And of course I also have to make the sum so that GROUPING SETS works properly. The question is answered for me. — Bettelbursche, Apr 27 '18 at 11:35

score 0 · Answer 1 · answered Apr 27 '18 at 10:14

Try this:

Select 
    bank,
    account,
    SUM(value1) value1, 
    SUM(value2) value2,
    SUM(value3) value3
from #MyTable
GROUP BY 
    GROUPING SETS ((bank, account, value1, value2, value3), (bank)) --

it should be equivalent to:

select *
from (
    select * from #MyTable
    union all
    Select 
        bank,
        NULL account,
        SUM(value1) value1, 
        SUM(value2) value2,
        SUM(value3) value3
    from #MyTable
    GROUP BY bank
) x
order by 1, 2 desc

but the 1st is more performant

Jay Shankar Gupta · Answer 2 · 2018-04-27T10:56:56.650

SELECT (CASE WHEN GROUPING(bank) = 0 
             AND  GROUPING(account) = 1 
             THEN 'Total ' + bank
             ELSE bank
        END) AS bank,
        account,
        SUM(value1) AS value1,
        SUM(value2) AS value2,
        SUM(value3) AS value3
FROM MyTable 
GROUP BY GROUPING SETS((bank, account,value1, value2, value3),
                       (bank)
                      );

Output

bank            account value1  value2  value3
NOSPA           Kto-X   1       1       1
NOSPA           Kto-Y   4       3       2
Total NOSPA     (null)  5       4       3
VR-Bank         Kto-A   1       2       3
VR-Bank         Kto-B   3       2       1
VR-Bank         Kto-C   4       4       5
Total VR-Bank   (null)  8       8       9

Demo

http://sqlfiddle.com/#!18/2c3b9/14

score 0 · Answer 3 · answered Apr 27 '18 at 10:51

If you want the original rows and to combine them using grouping sets, start by adding a unique id to the table:

Create Table #MyTable (
    mytableId int identity(1, 1) primary key,
    bank nvarchar(255),
    account nvarchar(255),
    value1 decimal(18,2),
    value2 decimal(18,2),
    value3 decimal(18,2),
);

I would be uncomfortable depending on the measured values to ensure that a row is unique.

Then you can simply do:

select bank, id, max(account) as account,
       sum(value1) as value1, sum(value2) as value2, sum(value3) as value3
from #MyTable
group by grouping sets ( (bank, id), (bank) );

If your intention is that bank/account is unique in combination, then you can do:

SELECT bank,account,
       SUM(value1), -- I would like this without SUM()
       SUM(value2),
       SUM(value3)
FROM #MyTable
GROUP BY GROUPING SETS ((bank, account), (account));

I need a new line with the sum of the previous lines

3 Answers3