Sum with conditions

Question

I have a table with three cloumns (tipo,ok,ko). I am trying to find a way to sum values based on conditions usint T-sql for example on the below table

Tipo	KO	OK	Totale
act-act	28	477	505
disktier	15	7665	Null
scopertura	105	475	580
Silver100%	21	3141	3162
Silverless50%	82	7665	Null
Silvermore50%	130	7665	Null
tra 2 siti	65	7665	Null

for each row i am trying to put the sum of the Ko + ok as totale but for sum rows i want to put a mutual total thus for the null values i want to sum all their ko's to the mutual ok so at the end i need to have 7,957 for all the 4 null values thus ko1(15) + ko2(82) +ko3(130) +ko4(65) + mutual ok (7665) so the desired result should look like the below table,

Tipo	KO	OK	Totale
act-act	28	477	505
disktier	15	7665	7957
scopertura	105	475	580
Silver100%	21	3141	3162
Silverless50%	82	7665	7957
Silvermore50%	130	7665	7957
tra 2 siti	65	7665	7957

here is the code that i'm using but its just sums the two columns for the first case i need to modify it to obtain the second case and obtain the desired result on the second table

select TIPO, KO, OK,
       sum(case when tipo in('act-act','scopertura','Silver100%') then (ko+ok)
                else null
           end ) as totale
From table
group by tipo , ok , ko 

Answer 1

You can use window functions to compute the overall count rather than a subquery - hence avoiding an additional (and unnecessary) table scan:

select t.*,
    case 
        when tipo in ('act-act','scopertura','Silver100%') 
            then ko + ok 
            else ok + sum(case when tipo not in ('act-act','scopertura','Silver100%') then ko else 0 end) over() 
    end totale
from mytable t

The sum(...) over() expression does the window sum of kos of the given tipo over the whole table.

If you don't want to repeat yourself, you can set a flag on tipo in a subquery first, then use it in the outer query:

select t.*,
    case 
        when flag_tipo = 1 
            then ko + ok 
            else ok + sum(case when flag_tipo = 0 then ko else 0 end) over() 
    end totale
from (
    select t.*, 
        case when tipo in ('act-act','scopertura','Silver100%') then 1 else 0 end as tipo_flag
    from mytable t  
) t