ASP Hosting sale!
Double disk space and transfer for FREE!
Limited time offer! Act Now!

aspdev | articles | tutorials | forums

 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

MS Access: Top 100, sum() and order by problem.

 
Post new topic   Reply to topic    ASPdev.org Forum Index -> SQL Programming (MS SQL Server, MS Access, MySQL)
View previous topic :: View next topic  
Author Message
CJ



Joined: 14 May 2005
Posts: 3

PostPosted: Sat May 14, 2005 7:56 pm    Post subject: MS Access: Top 100, sum() and order by problem. Reply with quote

I am having a bit of a logical problem with my SQL and hope someone can point me in the right direction.

I have a table (shortened here for brevity) that contains the following fields:

Yearly, CompanyID, Revenue.

Yearly simply contains a year, ex. 1990.
CompanyID is a foreign key to a Company table.
Revenue is a number.

This table contains the Revenue for various companies throughout the years, i.e. data may look something like this:
1990, 151, 120000
1990, 152, 150000
...
1991, 151, 125000
1991, 151, 145000
...
etc.

I need to extract the SUM of the TOP 100 Revenues for a specified year - or put another way, I need the SUM of the Revenue of the TOP 100 Companies, ordered by Revenue.

The problem is the combination of TOP, SUM() and ORDER BY as they simply do not mix well, i.e. something like:

select SUM(top 100 Revenue) from Yearlist where Yearly=1990 order by Revenue...

will, of course, not work as it is not valid SQL - but it does illustrate what I want.

Any pointers would helpful!

All the best...
Back to top
View user's profile Send private message
administrator
Site Admin


Joined: 01 Oct 2004
Posts: 183

PostPosted: Sat May 14, 2005 10:22 pm    Post subject: Reply with quote

You can do that with sub-select sql statement. Try something like that:

Code:

SELECT SUM(Revenue) from Yearlist where Yearly=1990 AND Company ID IN (SELECT TOP 100 CompanyID FROM Yearlist WHERE Yearly = 1990 ORDER BY Revenue DESC)


Let me know if it works for you?



Peter
_________________
Peter
ASP & ASP.NET Articles and Tutorials
Back to top
View user's profile Send private message
CJ



Joined: 14 May 2005
Posts: 3

PostPosted: Sun May 15, 2005 5:43 am    Post subject: Thank you! Reply with quote

Peter, you hit the head on the nail for me there, thank you.
Very obvious once it is seen, but for some reason I was blind.

You have actually solved another problem for me in the same go! To be able to select the same top 100 per Revenue but sort the result after another colonne in the table. This takes care of this neatly too.

I think you might have solved yet another puzzle for me as well: In the Company table there is a boolean (called Financial). What I really need is the sum of the top 100 companies where Financial=0 PLUS the top 15 where Financial=1.

I am planning to spare the Jet engine and just run two queries and then do the adding afterwards, but with your solution it would actually be possible with a simple OR between two IN criteria.
Problem is of course that would take two subselect, each with their own inner join to the Company table in order to get the Financial indicator.

Unless you have a better suggestion? (I have been working so long on this app that I think I have gotten 'snow blind'.)

Anyway, didn't want to squeeze the last bit from you so if there is not further 'shortcut' in this matter, you have still helped me a lot. Thank you for taking the time! Smile

All the best.
Back to top
View user's profile Send private message
administrator
Site Admin


Joined: 01 Oct 2004
Posts: 183

PostPosted: Mon May 16, 2005 8:47 am    Post subject: Reply with quote

Thanks CJ Smile

I agree that running 2 separate queries for the 2 SUMs is easier here.
I would do it that way.


Have a nice day!
_________________
Peter
ASP & ASP.NET Articles and Tutorials
Back to top
View user's profile Send private message
CJ



Joined: 14 May 2005
Posts: 3

PostPosted: Mon May 16, 2005 9:18 am    Post subject: Reply with quote

Yes, I have infact already done it that way.

Only draw-back is one of speed, but that was to be predicted.

Thanks again for your suggestion, it put me on track.

CJ


EDIT: Oops, misread your reply. I actually settled for the two sub-queries with each their own inner join, the reason due to further issues not included in my post (there are a lot of other values that need to be extracted but with the same logic). Hence my above reference to speed.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    ASPdev.org Forum Index -> SQL Programming (MS SQL Server, MS Access, MySQL) All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2002 phpBB Group

SQL Tutorial