I've data like below in a table cryptotransactionledger

If I spend bitcoin, I'm putting a new entry like below in the same table with transaction_typeid 3 and similarly for ethereum with transaction_typeid 4

Suppose if my final data in the table is like below, I will have 35 bitcoin and 20 ethereum remaning.

How can I arrive at below balance summary using SQL after reducing all debits from respective credits

CodePudding user response：

Assuming you have the tables ledger and coin_types:

create table ledger (id varchar2(3) primary key, 
                 coin_type varchar2(2), 
                 amount number(5)
                 );

create table coin_types (id varchar2(2) primary key, 
                         alias varchar2(100));

These DML-Statements are representing your "final data":

insert into coin_types values (1, 'bitcoin');
insert into coin_types values (2, 'etherium');

insert into ledger values (1, 1, 30);
insert into ledger values (2, 2, 20);
insert into ledger values (3, 1, -10);
insert into ledger values (4, 2, -5);
insert into ledger values (5, 1, 15);
insert into ledger values (6, 2, 10);
insert into ledger values (7, 2, -5);

Your first step should be to sum up the amounts, grouped by your coin type:

  select l.coin_type, 
         c.alias,
         sum(l.amount) amount_coin
    from ledger l, coin_types c
   where l.coin_type = c.id
group by l.coin_type, c.alias

You can then use this select to get your "totalcoins" as a cumulative sum, ordered by your coin_type (see Oraclee Docs):

   select coin_type, 
          alias, 
          amount_coin, 
          sum(amount_coin) over (order by coin_type) totalcoins 
      from(
          select l.coin_type, 
                 c.alias,
                 sum(l.amount) amount_coin
            from ledger l, coin_types c
           where l.coin_type = c.id
        group by l.coin_type, c.alias
           );

Here is a working SQL-Fiddle: SQL-Fiddle

CodePudding user response：

I would forget "transaction types" for the final result and just focus on the coin itself. You data model is rather strange, because you don't have a separate column for the type of coin. But you can extract it from the transaction_type column.

In addition, your comments suggest that totalcoins should be the last value in the column. For "bitcoin" that would be 50, not 35.

In any case, you can use aggregation. I would suggest:

select regexp_substr(transaction_type, '^[^-] ') as coin,
       sum(amount) as amount,
       max(totalcoins) keep (dense_rank first oder by id desc) as totalcoins
from cryptotransactionledger tl
group by regexp_substr(transaction_type, '^[^-] ');

Here is a db<>fiddle.

CodePudding user response：

I would create a table of tran types

create table tran_types (
   id int primary key,
   coin_Name varchar2(100),
   op_Name varchar2(100)
   );

insert into tran_types  
select 1, 'bitcoin', 'credit' from dual union all
select 2, 'etherium','credit' from dual union all
select 3, 'bitcoin', 'debit'  from dual union all
select 4, 'etherium','debit'  from dual;

This way you can group transactions to get totals and visualise total row headers as needed

select t.id transaction_typeid, t.coin_Name || '-' || t.op_Name transaction_type, 
    s.amount, s.total_coins
from (
  select t.coin_Name, sum(amount) amount
    , sum(sum(amount)) over(order by t.coin_Name) total_coins
  from ledger r
  join tran_types t on r.tran_type = t.id
  group by t.coin_Name
) s
join tran_types t on t.op_name = 'credit' and t.coin_Name = s.coin_Name
order by t.coin_Name;