Query for Top N for large amount of data 1

[AccountCR]

I have a database that has 5M rows of data in it. The table is setup as such:

Invoice Table

Date
Salesperson
State
Cost

We are trying to get a query that shows the 5 sales people with the most sales $ PER STATE.

Thanks for the help.

 

[memdiscount]

Hi;

You can try this query, I did not test it but wrote for you from the information given;

Select top 5 SalesPerson, State , Max(Cost)
From InvoiceTable
Group by Salesperson , State
Order by Max(Cost) Desc

Let me know if it works for you.

Thanks

 

[AlexCuse]

Have a look at this:

http://www.alexcuse.com/blogentry.aspx?postID=26

if you have SQL 2005, the ROW_NUMBER() method would probably work a lot better due to your table size (there's a link at the bottem of that one)

You can also do this with a temp table and an identity column.

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[AccountCR]

memdiscount,

The query you provided gives me the sales reps with the highest sales item entered but doesn't give me the top 5 sales reps PER STATE.

I should have been a little clearer about the data entered. Each item in the table represents an invoice for a rep for a state on a particular date. Each rep can have multiple entries for invoices on different dates for the same state.

I would like to know what query I would have to run to get the top 5 sales reps PER STATE. This includes the total sales for that state for that rep.

Thanks for your help.

 

[chamilz]

if it is SQL 2005, you could try this.

i used multiple CTEs along with the ROW_NUMBER() function.

with SALES1 as
(
select SalesPerson,State,sum(Cost) Tot_Cost
from InvoiceTable
group by SalesPerson,State
),
SALES2 as
(
select SalesPerson,State, row_number() over(partition by State order by Tot_Cost desc) t_rank
from SALES1
)
select SalesPerson,State,Tot_Cost
from SALES2
where t_rank in (1,2,3,4,5)

 

[eerlee]

hi, try this

SELECT SalesPerson, State, SUM(Cost) AS TotalSales
FROM Invoice I
WHERE SalesPerson IN (SELECT TOP 5 SalesPerson FROM Invoice WHERE State=I.State GROUP BY SalesPerson ORDER BY SUM(Cost) DESC)
GROUP BY SalesPerson, State

 

[AccountCR]

eerlee,

Thanks for your query. This works but it takes a long time to run, actually around 24 min on a hoss machine. I am running SQL Server 2000. Is there a faster query that can be run?

 

[gmmastros]

Did you look at the link that Alex provided for you? It will accomplish your goals and run faster too.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AccountCR]

I did look at it. Maybe I am just a little dense but I couldn't see how to incorporate his query to return a result set of the following for the table that we have.

Tom 345.45 AL
Joe 325.25 AL
Moe 225.25 AL
Jim 125.25 AL
Sue 110.54 AL
Guy 234.12 MI
...

Would someone mind helping me out on that by posting the query that I would use? Thanks for the help.

 

[ca8msm]

Would someone mind helping me out on that by posting the query that I would use?

Try having a go yourself and pasting your attempts here if you do not get what you are looking for. People will be glad to help you, but they do not want to do your work for you.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[gmmastros]

Try this...

SET NOCOUNT ON
Declare @Sums 
Table   (RowId Int Identity(1,1), 
        Rank Int, 
        SalesPerson VarChar(20), 
        State VarChar(20), 
        SumOfCost Numeric(10,4) 
        Primary Key (State, SumOfCost, SalesPerson))

Insert Into @Sums(SalesPerson, State, SumOfCost)
Select SalesPerson, State, Sum(Cost)
from   InvoiceTable
Group By SalesPerson, State
Order By State, Sum(Cost) DESC

Update @Sums
Set    Rank = RowId - MinRowId + 1
From   @Sums S
       Inner Join (
         Select State, Min(RowId) As MinRowId
         From   @Sums
         Group By State
         ) as A 
         On S.State = A.State

Select State, SalesPerson, SumOfCost
From   @Sums
Where  Rank <= 5
Order BY State, SumOfCost DESC

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AccountCR]

OK. Here is what I tried:

Select a.State, a.SalesPerson, Sum(a.Cost) as TotalSales
from dbo.JBMTest a
inner join dbo.JBMTest b
on a.SalesPerson = b.SalesPerson and
Sum(a.Cost) <= Sum(b.Cost)
group by a.SalesPerson, a.State
having count(a.State) <= 5
order by a.state, Sum(a.cost) desc

This won't work because of Aggregrate functions in the ON statement.

I also tried

Select a.State, a.SalesPerson, Sum(a.Cost) as TotalSales
from dbo.JBMTest a
inner join dbo.JBMTest b
on a.SalesPerson = b.SalesPerson
group by a.SalesPerson, a.State
having count(a.State) <= 5
order by a.state, Sum(a.cost) desc

This returns zero results. I'm not sure what I am missing on this. Any help would be appreciated.

 

[AccountCR]

Thanks gmmastros. That did it. I really appreciate all your help.

 

[gmmastros]

Just out of curiosity.... how long does it take to execute?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AccountCR]

10 seconds on our SQL 2005 machine and 49 secs on our SQL 2000 machine. Huge improvement.