Select Sum Top 5 per region - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Select Sum Top 5 per region

Author

Topic

SQLNoob81
Starting Member

38 Posts

Posted - 2013-11-08 : 05:58:57

Hi Guys,

I have a table with the following fields:

Consultant - Fee - Branch

I need to return the sum(fee) of the Top 5 consultant per branch.

Sounds simple but cant get my head around it.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-08 : 08:08:32

Is Region another column in your table? If you want top 5 per branch you can do the following. If you want to calculate the top 5 for the region, you have to have some table where regions are listed and can be related to branches.

SELECT
	a.Branch,
	b.Consultant,
	b.Fees
FROM
	YourTable a
	OUTER APPLY
	(
		SELECT TOP (5) /* WITH TIES */ b.Consultant, SUM(b.Fee) AS Fees
		FROM YourTable b
		WHERE b.branch = a.branch
		GROUP BY b.Consultant
		ORDER BY SUM(b.Fee) DESC 
	) b

SQLNoob81
Starting Member

38 Posts

Posted - 2013-11-08 : 08:25:13

Hi James, It's not returning the required info. This is what I would need to be returned.

Branch3 - $500 000 *(the $500 000 would be the Sum(Fee) of the top5 consultants in Branch3)
Branch6 - $450 000 *(the $450 000 would be the Sum(Fee) of the top5 consultants in Branch6)
Branch1 - $300 000 *(the $300 000 would be the Sum(Fee) of the top5 consultants in Branch1)
Branch5 - $250 000 *(the $250 000 would be the Sum(Fee) of the top5 consultants in Branch5)
Branch2 - $200 000 *(the $200 000 would be the Sum(Fee) of the top5 consultants in Branch2)
Branch4 - $100 000 *(the $100 000 would be the Sum(Fee) of the top5 consultants in Branch4)

Each branch has more than 10 consultant but I only want the sum(fee) of the top 5

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-08 : 08:35:37

Can one consultant have more than one record for a given branch? If so this won't work. If it doesn't work, can you post sample data from your source table?

SELECT
	a.Branch,
	SUM(b.Fee) as Fees
FROM
	YourTable a
	OUTER APPLY
	(
		SELECT TOP (5) /* WITH TIES */  b.Fee
		FROM YourTable b
		WHERE b.branch = a.branch
		ORDER BY b.Fee DESC 
	) b
group by a.Branch

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-11-08 : 08:37:41

Try this:
[CODE]

DECLARE @banks TABLE(Consultant VARCHAR(20), Branch VARCHAR(20), Fee NUMERIC(20,2));
INSERT INTO @banks VALUES
('John', 'Branch1', 1000.00),
('Jill', 'Branch1', 1600.00),
('Jes', 'Branch1', 1200.00),
('John1', 'Branch1', 1001.00),
('Jill1', 'Branch1', 1601.00),
('Jes1', 'Branch1', 1201.00),
('Joe', 'Branch2', 2000.00),
('Jen', 'Branch2', 2300.00),
('Jens', 'Branch2', 2501.00),
('Joe1', 'Branch2', 2001.00),
('Jen1', 'Branch2', 2301.00),
('Jens1', 'Branch2', 2501.00),
('Jim1', 'Branch3', 2201.00 ),
('Jones1', 'Branch3', 2601.00 ),
('James1', 'Branch3', 2801.00 ),
('Jim', 'Branch3', 2200.00 ),
('Jones', 'Branch3', 2600.00 ),
('James', 'Branch3', 2800.00 );

WITH CTE AS
(SELECT ROW_NUMBER() OVER(PARTITION BY Branch ORDER BY FEE DESC) RN, Branch, Consultant, Fee From @banks)
SELECT SUM(Fee) as TopFee, Branch from CTE Where RN <= 5 GROUP BY Branch

[/CODE]

SQLNoob81
Starting Member

38 Posts

Posted - 2013-11-08 : 09:01:58

@James,

Consultants can have unlimited records per branch.

@MuMu

Yours seemed to work but when I checked by adding up manually the results where not the same!

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-11-08 : 09:26:18

quote:
Originally posted by SQLNoob81
[br

@MuMu

Yours seemed to work but when I checked by adding up manually the results where not the same!

Can you show us some of your data.

SQLNoob81
Starting Member

38 Posts

Posted - 2013-11-11 : 02:00:06

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 02:54:55

[code]
SELECT TOP 5 WITH TIES Consultant,Branch,SUM(fee) AS Totalfee
FROM Table
GROUP BY Consultant,Branch
ORDER BY Totalfee DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SQLNoob81
Starting Member

38 Posts

Posted - 2013-11-11 : 03:47:52

Hi, That gave me the top 5 Consultants.

DBN Tracy Redfern 34418036.204
DBN Elmarie Muhl 29176156.115
DBN Leoni Coleman 24551884.2492
DBN Lara Diamond 24040733.1516
DBN Laura Knight 21173778.4935

I need the sum of the top 5 consultants fee per branch.

E.g.

DBN 500000
JHB 400000
CPT 350000
PTA 230000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 06:22:09

Oh ..i see..then here you go


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 Table
GROUP BY Consultant,Branch
)t
WHERE Rn <=5
GROUP BY Branch

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SQLNoob81
Starting Member

38 Posts

Posted - 2013-11-12 : 01:53:19

Hi visakh16.

That worked spot on, thanks so much for your help. I really Appreciate it!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-12 : 02:08:58

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SQLNoob81
Starting Member

38 Posts

Posted - 2013-12-03 : 02:02:49

Hi visakh16
I picked up an issue with your query. If for instance the there is a tie at 3 place (they have the exact same value) it adds them together, and still counts the 4th and 5th place.

Any idea how to overcome this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-03 : 02:46:36

quote:
Originally posted by SQLNoob81

Hi visakh16
I picked up an issue with your query. If for instance the there is a tie at 3 place (they have the exact same value) it adds them together, and still counts the 4th and 5th place.

Any idea how to overcome this?

so do you mean in any case you need to consider only first 5? what if tie is for 5th place (ie multiple consultants with same value at 5th place)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SQLNoob81
Starting Member

38 Posts

Posted - 2013-12-04 : 01:07:40

Yep only the top 5. Then it only uses one of the values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-04 : 05:23:41

this will just take first 5 rows even in case of any existing ties


SELECT Branch,SUM(Totalfee) AS BranchTop5Total
FROM
(
SELECT Consultant,Branch,SUM(fee) AS Totalfee,
ROW_NUMBER() OVER (PARTITION BY Branch ORDER BY SUM(fee) DESC) AS Rn
FROM Table
GROUP BY Consultant,Branch
)t
WHERE Rn <=5
GROUP BY Branch

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SQLNoob81
Starting Member

38 Posts

Posted - 2013-12-09 : 02:34:48

Thanks visakh16, That works perfectly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-09 : 06:36:39

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources