SQL showing summary after each column value changes

Question

I have sales data for branches. I want a SQL which will give me summary of sales data for each branchId as below, of course BranchId(s) are huge so I have to make it dynamic (I can't use Union). I am stuck how to add a summary row after every branch change dynamically.

+ ---------+--------+---------+-----------+
| BranchId | CashIn | CashOut | CardSales |
+ ---------+--------+---------+-----------+
| 1        |   1000 |     500 |        50 |
| 1        |    500 |    2500 |       100 |
| 1        |   1000 |     200 |       200 |
| Totals   |   2500 |    3200 |       350 |
| 5        |    100 |     500 |       500 |
| Totals   |    100 |     500 |       500 |
| 7        |    100 |     100 |       100 |
| 7        |    200 |     300 |       400 |
| Totals   |    300 |     400 |       500 |
+ ---------+--------+---------+-----------+

Possible duplicate of [SQL Oracle query to calculate subtotal](https://stackoverflow.com/questions/43979507/sql-oracle-query-to-calculate-subtotal) — JohnHC, May 21 '19 at 14:57

Gordon Linoff · Accepted Answer · 2019-05-21T20:09:13.057

1

A brute force method is to do the aggregation and then interleave the results:

select (case when is_base = 1 then to_char(BranchId)
             else replace('Total ([BranchId])', '[BranchId]', BranchId)
        end) as BranchId, CashIn, CashOut, CardSales
from ((select BranchId, CashIn, CashOut, CardSales, 1 as is_base
       from t
      ) union all
      (select BranchId, sum(CashIn), sum(CashOut), sum(CardSales), 0 as is_base
       from t
       group by BranchId
      )
     ) t
order by t.BranchId, is_base desc;

Here is a db<>fiddle.

edited May 21 '19 at 20:09

answered May 21 '19 at 15:12

Gordon Linoff

1,242,037
58
646
786

@AnkurBhutani . . . I added a db<>fiddle and fixed a couple of typos. – Gordon Linoff May 21 '19 at 20:13

SQL showing summary after each column value changes

1 Answers1