top 20 records by group

[FB]

Need to select top 20 records by location based on the sum of balances. Here
is what I have so far, but it is only giving me the top 20 of all not of each
location; your suggestion is welcome.

Thank you


SELECT TOP 20 account.ProfCtr, account.Own1SSN, account.Own1Name,
Sum(([currentBal])) AS [Total Balance], Count(account.Own1Name) AS [count]
FROM account
GROUP BY account.ProfCtr, account.Own1SSN, account.Own1Name
ORDER BY account.ProfCtr, Sum(([currentBal])) DESC;

 

[KARL DEWEY]

This will get you the top 20 per location but you need to sum ahead of this ---
SELECT Q.ProfCtr, Q.Own1SSN, Q.Own1Name, Q.currentBal, (SELECT COUNT(*) FROM
account Q1
WHERE Q1.[ProfCtr] = Q.[ProfCtr]
AND Q1.[currentBal] < Q.[currentBal])+1 AS Rank
FROM account AS Q
WHERE ((((SELECT COUNT(*) FROM account Q1
WHERE Q1.[ProfCtr] = Q.[ProfCtr]
AND Q1.[currentBal] < Q.[currentBal])+1)<=20))
ORDER BY Q.ProfCtr, Q.currentBal;

 

[Ken Sheridan]

I'm not sure I've understood you correctly, but try this:

SELECT PofCtr, Own1SSN, Own1Name,
SUM(CurrentBal) AS TotalBal,
(SELECT COUNT(*)
FROM Account AS A2
WHERE A2.PofCtr = A1.PofCtr
AND A2.Own1SSN = A1.Own1SSN) AS AccCount
FROM Account As A1
GROUP BY PofCtr, Own1SSN, Own1Name
HAVING SUM(CurrentBal) IN
(SELECT TOP 20 Sum(CurrentBal)
FROM Account As A2
WHERE A2.PofCtr = A1.PofCtr
GROUP BY Own1SSN
ORDER BY SUM(CurrentBal) DESC)
ORDER BY PofCtr, SUM(CurrentBal) DESC;

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

I'm not sure I've understood you correctly, but try this:

SELECT PofCtr, Own1SSN, Own1Name,
SUM(CurrentBal) AS TotalBal,
(SELECT COUNT(*)
FROM Account AS A2
WHERE A2.PofCtr = A1.PofCtr
AND A2.Own1SSN = A1.Own1SSN) AS AccCount
FROM Account As A1
GROUP BY PofCtr, Own1SSN, Own1Name
HAVING SUM(CurrentBal) IN
(SELECT TOP 20 Sum(CurrentBal)
FROM Account As A2
WHERE A2.PofCtr = A1.PofCtr
GROUP BY Own1SSN
ORDER BY SUM(CurrentBal) DESC)
ORDER BY PofCtr, SUM(CurrentBal) DESC;

Ken Sheridan
Stafford, England

 

[Allen Browne]

Karl and Ken have answered your question, by using a subquery.

If subqueries are a new concept, here's an introduction:
http://allenbrowne.com/subquery-01.html#TopN

 

[Fran]

I have the exact same question so I tried both suggestions but seems to be
running in a never ending loop. I created a local table with the sum of
current by customer by location. Then I used the first suggestion to get me
the top 20 highest balances by location, but it has been running for a very
long time.

This will get you the top 20 per location but you need to sum ahead of this ---
SELECT Q.ProfCtr, Q.Own1SSN, Q.Own1Name, Q.currentBal, (SELECT COUNT(*) FROM
account Q1
WHERE Q1.[ProfCtr] = Q.[ProfCtr]
AND Q1.[currentBal] < Q.[currentBal])+1 AS Rank
FROM account AS Q
WHERE ((((SELECT COUNT(*) FROM account Q1
WHERE Q1.[ProfCtr] = Q.[ProfCtr]
AND Q1.[currentBal] < Q.[currentBal])+1)<=20))
ORDER BY Q.ProfCtr, Q.currentBal;

--
KARL DEWEY
Build a little - Test a little

Need to select top 20 records by location based on the sum of balances. Here
is what I have so far, but it is only giving me the top 20 of all not of each
location; your suggestion is welcome.

Thank you


SELECT TOP 20 account.ProfCtr, account.Own1SSN, account.Own1Name,
Sum(([currentBal])) AS [Total Balance], Count(account.Own1Name) AS [count]
FROM account
GROUP BY account.ProfCtr, account.Own1SSN, account.Own1Name
ORDER BY account.ProfCtr, Sum(([currentBal])) DESC;