Top n - Groupwise - 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

General SQL Server Forums

New to SQL Server Programming

Top n - Groupwise

Author

Topic

LMorton
Starting Member

4 Posts

Posted - 2007-09-14 : 10:14:59

I am trying to create a query that will show me the top 10 customers for each one of my sales reps. I can get it to give me the top 10 customers for any rep, but I'm not sure how to alter the code to give me top 10 for each rep. Any help would be appreciated.

SELECT TOP 10 T.CUSTOMERS, SUM(T.AMT_0) AS AMT_0
FROM SNXPROD.T_CUSTITM_TTL T
JOIN SNXPROD.BPCUSTOMER C ON C.BPCNUM_0 = T.BPR_0
GROUP BY T.BPR_0
ORDER BY SUM(T.AMT_0) DESC

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 10:18:01

How do you denote the REP column?

Please remember, this is the absolutely first time we get a glimpse of your tables.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-14 : 10:18:08

See: http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Item (2)

Kristen

LMorton
Starting Member

4 Posts

Posted - 2007-09-14 : 10:21:57

Rep column is REP in the SNXPROD.BPCUSTOMER table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 10:23:43

Something similar to this

SELECT	q.SalesRepID,
	q.CustomerID,
	qu.Amount
FROM	(
		SELECT	u.SalesRepID,
			u.CustomerID,
			u.Amount,
			ROW_NUMBER() OVER (PARTITION BY u.SalesRepID ORDER BY u.Amount DESC) AS RecID
		FROM	(
				SELECT		c.SalesRepID,
						c.CustomerID,
						SUM(s.Amount) OVER (PARTITION BY c.CustomerID) AS Amount,
				FROM		Customers AS c
				INNER JOIN	Sales AS s ON s.CustomerID = c.CustomerID
			) AS u
	) AS q
WHERE	q.RecID BETWEEN 1 AND 10

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-14 : 10:56:08

quote:

Originally posted by Peso

Something similar to this

SELECT	q.SalesRepID,
	q.CustomerID,
	qu.Amount
FROM	(
		SELECT	u.SalesRepID,
			u.CustomerID,
			u.Amount,
			ROW_NUMBER() OVER (PARTITION BY u.SalesRepID ORDER BY u.Amount DESC) AS RecID
		FROM	(
				SELECT		c.SalesRepID,
						c.CustomerID,
						SUM(s.Amount) OVER (PARTITION BY c.CustomerID) AS Amount,
				FROM		Customers AS c
				INNER JOIN	Sales AS s ON s.CustomerID = c.CustomerID
			) AS u
	) AS q
WHERE	q.RecID BETWEEN 1 AND 10

E 12°55'05.25"
N 56°04'39.16"

Provided OP should use SQL Server 2005

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 11:03:25

If he is NOT using SQL Server 2005, maybe next time he learns to provide that vital piece of information?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-14 : 11:10:07

"next time he learns to provide that vital piece of information?"

How will a new user know that you need to know that if he posts in the "new to SQL Server" forum?

LMorton
Starting Member

4 Posts

Posted - 2007-09-14 : 11:28:40

SQL2000

Kristen
Test

22859 Posts

Posted - 2007-09-14 : 11:41:39

For SQL2000 you can't use the OVER (PARTITION BY ... solutions above.

Did you try the link I posted earlier?

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 11:54:30

They still learn SQL Server 2000, after three year since SQL Server 2005 is out?

E 12°55'05.25"
N 56°04'39.16"

LMorton
Starting Member

4 Posts

Posted - 2007-09-14 : 11:55:26

Not yet, I will try that this afternoon.

Kristen
Test

22859 Posts

Posted - 2007-09-14 : 21:48:49

"They still learn SQL Server 2000, after three year since SQL Server 2005 is out?"

Sure, why not?

All our web servers, except for one, are still SQL 2000. The costs of licences for the clients to upgrade are astronomic! as is their cost of the effort to run a complete acceptance test of the whole application.

Now, if they feel the need to upgrade to a newer version of our software they will have to tool up for the acceptance test, so then it is no more work to do acceptance test of a new SQL Server version at the same time. But they may not want the cost of SQL Service licences - which probably means having a new machine because they won't sanction installion on their existing web servers in case it interfers with the production applications ....

... age old problem of course!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 01:29:18

quote:
Originally posted by Peso

They still learn SQL Server 2000, after three year since SQL Server 2005 is out?

E 12°55'05.25"
N 56°04'39.16"

As I told you earlier, not every company switches over to SQL Server 2005

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources