How to add the total row after the blank line?-CodePudding

How to add the total row at the end with all the totals? When I run the query, I want to see the row after the blank line, with totals. Please look at the table below. If the column is not summable then that should be blank as well.

    select 
    t.Business_Unit_Description,
    billable_trades, 
    @rate rate, 
    billable_trades * @rate as charge,
    isnull(c.comm_adjustments, 0) as commission_adjustments,
    0.3 commission_adj_rate,
    isnull(c.comm_adjustments, 0) * 0.3 * -1 as credit,
    ((billable_trades * @rate)   (isnull(c.comm_adjustments, 0) * 0.3 * -1)) as total
  from   
       (
       select Business_Unit_Description, sum(billable_trades) as billable_trades
       from   #cte_combined
       group by Business_Unit_Description
       ) t
       left outer join #cte_comm_adj c on c.Business_Unit_Description = 
    t.Business_Unit_Description
   order by t.Business_Unit_Description

The data looks like this:

CodePudding user response：

Assume that you table contains information about Company, Charge, Commission and you want to get total per each company and also per each of its column. As you didn't provide any table schemas or your data sample, I created a table, populated with data from your question.

I'd suggest the next query:

create table test
(
    Company varchar(20) not null,
    Charge int not null,
    Commission int not null
)

insert into test
values 
('A', 50, 20),
('B', 23, 10),
('C', 80, 23),
('D', 60, 12)

select Company, Charge, Commission, SUM(Charge   Commission) OVER (PARTITION BY Company ORDER BY Company ASC) as total 
from test
union all
select 'Total', SUM(Charge), SUM(Commission), SUM(Charge   Commission)
from test