I have a below table where I want an output of total ID and "Total Trade Role" per "Branch". For example for the branch Kent we have a count of 3 "Trade ID" and "Total Trade Role"= 5 because TradeID 12232 holds 2 member ID and TradeID 6556 holds 1 Member ID.

Any help on how can I count total member ID and Total Trade role against each Branch?

CodePudding user response：

with cte as
(
    select
      branch,
      id,
      TradeID,
      TotalTradeRole,
      DENSE_RANK() over (partition by branch order by tradeid) as denbrach
    from #traderole
),
sum_brach as
(
    select
      branch,
      sum(totaltraderole) over (partition by denbrach, branch order by totaltraderole) as totaltraderole
    from cte 
    group by
      branch,
      denbrach,
      totaltraderole 
),
got_sum as
(
    select
      branch,
      sum(totaltraderole) as totaltraderole
    from sum_brach
    group by
      branch
),
total_tradeid as
(
    select
      branch,
      count(tradeid) as totalid
    from #traderole
    group by
      Branch
)
select
  g.branch as Branch,
  tt.totalid as [Total ID],
  g.totaltraderole as [Total Trade Role]
from got_sum g
inner join total_tradeid tt on g.branch = tt.branch