How to substract from one row the other ones in SQL Clickhouse

Question

I have a problem with the substraction of rows. For instance, after the executing this query:

select 
    case 
        when source in ('all') then 'all'
        when source in ('source1') then 'source1'
        when source in ('source2') then 'source2'
        when source in ('source3') then 'source3'
    end as source,
    
    sum(value) as sum
    
from table
group by 
    source

I have a table:

source	sum
all	100
source 1	1
source 2	1
source 3	1

And I want to add to this table the new row with 'all-source1-source2-source3' value of 100-1-1-1=97. Could you, please, help me?

Answer 1

select (arrayJoin(flatten([sa, ss, sd])) as x).1 source, x.2 sum
FROM (
       select groupArrayIf((source, sum), source='all') sa, 
              groupArrayIf((source, sum), source!='all') ss,
              [('diff', arraySum(sa.2) - arraySum(ss.2))] sd
       from (
               select  'all'  source, 100 sum union all
               select  'source 1', 1 union all
               select  'source 2', 1 union all
               select  'source 3', 1 
       )
    )

┌─source───┬─sum─┐
│ all      │ 100 │
│ source 1 │   1 │
│ source 2 │   1 │
│ source 3 │   1 │
│ diff     │  97 │
└──────────┴─────┘