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

CJ · Joined: 14 May 2005 Posts: 3

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...

administrator · Site Admin Joined: 01 Oct 2004 Posts: 183

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

CJ · Joined: 14 May 2005 Posts: 3

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.

administrator · Site Admin Joined: 01 Oct 2004 Posts: 183

Thanks CJ

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

CJ · Joined: 14 May 2005 Posts: 3

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.