Sum of top 5 Consultants per Branch

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 514
Each branch can have many consultants.... Here we would like to sum up the TOP 5 Consultant's FEE in each branch

--Sample Code
DECLARE @tab TABLE (Consultant varchar(500), Branch varchar(10), Fee int)
insert @tab
SELECT 'Tracy Redfern ', 'DBN', 24000 union all
SELECT 'Maria Kaladzis ', 'JHB', 66000 union all
SELECT 'Tamlyn McCormick ', 'PTA', 17600 union all
SELECT 'Chanelle Rorke ', 'Dbn', 23400 union all
SELECT 'Chivah Jones ', 'PTA', 21600 union all
SELECT 'Ronelle Galletti ', 'JHB', 21600 union all
SELECT 'Chivah Jones ', 'PTA', 13800 union all
SELECT 'Natasha Berridge ', 'EL', 29160 union all
SELECT 'Leoni Coleman ', 'DBN', 18000 union all
SELECT 'Natasha Berridge ', 'EL', 9720 union all
SELECT 'Chivah Jones ', 'PTA', 21600 union all
SELECT 'Tamlyn McCormick ', 'PTA', 10400 union all
SELECT 'Sibongile Masombuka ', 'JHB', 45000 union all
SELECT 'Leoni Coleman ', 'DBN', 50000 union all
SELECT 'Tamlyn McCormick ', 'PTA', 40500 union all
SELECT 'Sibongile Masombuka ', 'Isando', 45000 union all
SELECT 'Sibongile Masombuka ', 'JHB', 45000 union all
SELECT 'Rayaan Manan ', 'PE', 60000 union all
SELECT 'Shannon Joyner ', 'PE', 10200 union all
SELECT 'Lachae Leo ', 'PE', 18590 union all
SELECT 'Shannon Joyner ', 'PE', 12000 union all
SELECT 'Shereen Christoffels ', 'POL', 0 union all
SELECT 'Ronelle Galletti ', 'JHB', 30000 union all
SELECT 'Ronelle Galletti ', 'JHB', 30000 union all
SELECT 'Natasha Berridge ', 'EL', 17971.2 union all
SELECT 'Tracy Wicks ', 'NEL', 34560 union all
SELECT 'Andrea Porter ', 'DBN', 1040 union all
SELECT 'Janine Coxhill ', 'PE', 0 union all
SELECT 'Ferdy Petersen ', 'CPT', 108000 union all
SELECT 'Deveshnee Govindasamy ', 'DBN', 22500 union all
SELECT 'Ferdy Petersen ', 'CPT', 90000 union all
SELECT 'Ferdy Petersen ', 'CPT', 27000 union all
SELECT 'Chanelle Rorke ', 'DBN', 18000 union all
SELECT 'Laura Knight ', 'DBN', 42084.9 union all
SELECT 'Tilly vd Merwe ', 'PTA', 16050 union all
SELECT 'Ronelle Galletti ', 'JHB', 29029.5 union all
SELECT 'Ronelle Galletti ', 'JHB', 75000 union all
SELECT 'Deveshnee Govindasamy ', 'DBN', 6750 union all
SELECT 'Chivah Jones ', 'PTA', 8325.33 union all
SELECT 'Stephanie Snyman ', 'DBN', 13500 union all
SELECT 'Ronelle Galletti ', 'JHB', 64800 union all
SELECT 'Syanne Allmark ', 'JHB', 30000 union all
SELECT 'Natasha Hurst ', 'DBN', 6120 union all
SELECT 'Laura Knight', 'DBN', 20000 union all
SELECT 'Donne van Tonder', 'PE', 8640 union all
SELECT 'Tamlyn McCormick', 'PTA', 25200 union all
SELECT 'Donne van Tonder Port', 'Elizabeth', 56700 union all
SELECT 'Syanne Allmark', 'JHB', 93600 union all
SELECT 'Syanne Allmark', 'JHB', 32400 union all
SELECT 'Tamlyn McCormick', 'PTA', 52500


-- sum of top 5 consultants per branch
SELECT Branch,SUM(Totalfee) AS BranchTop5Total
FROM
(
SELECT Consultant,Branch,SUM(fee) AS Totalfee,
DENSE_RANK() OVER (PARTITION BY Branch ORDER BY SUM(fee) DESC) AS Rn
FROM @tab
GROUP BY Consultant,Branch
)t
WHERE Rn <=5
GROUP BY Branch

Comments or Responses

Login to post response