SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Select Sum Top 5 per region
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 11/08/2013 :  05:58:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 11/08/2013 :  08:08:32  Show Profile  Reply with Quote
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

Edited by - James K on 11/08/2013 08:09:33
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 11/08/2013 :  08:25:13  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 11/08/2013 :  08:35:37  Show Profile  Reply with Quote
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

Edited by - James K on 11/08/2013 08:36:04
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 11/08/2013 :  08:37:41  Show Profile  Reply with Quote
Try this:


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



Edited by - MuMu88 on 11/08/2013 08:41:34
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 11/08/2013 :  09:01:58  Show Profile  Reply with Quote
@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!
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 11/08/2013 :  09:26:18  Show Profile  Reply with Quote
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.
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 11/11/2013 :  02:00:06  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/11/2013 :  02:54:55  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 11/11/2013 :  03:47:52  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/11/2013 :  06:22:09  Show Profile  Reply with Quote
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
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 11/12/2013 :  01:53:19  Show Profile  Reply with Quote
Hi visakh16.

That worked spot on, thanks so much for your help. I really Appreciate it!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/12/2013 :  02:08:58  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 12/03/2013 :  02:02:49  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/03/2013 :  02:46:36  Show Profile  Reply with Quote
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
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 12/04/2013 :  01:07:40  Show Profile  Reply with Quote
Yep only the top 5. Then it only uses one of the values.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/04/2013 :  05:23:41  Show Profile  Reply with Quote
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
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 12/09/2013 :  02:34:48  Show Profile  Reply with Quote
Thanks visakh16, That works perfectly.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/09/2013 :  06:36:39  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000