List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:April 25 2006 3:26pm
Subject:Re: Top N selections + rest row
View as plain text  
Hi Cor, all!


C.R.Vegelin wrote:
> Hi Joerg, All,
> 
> I would like to have something like:
> Country      Type    2004        2005
> --------------------------------------
> Germany    Sales    13357      19843
> Belgium      Sales    12224      16767
> France       Sales    15443      16602
> Un. States  Sales    11995      14332
> Japan         Sales    14234      13364
> Rest           Sales    17663      12563
> --------------------------------------
> if a user requires a Top-5 selection for 2005,
> and where Totals of both 2004 and 2005 are 100%.


My impression is your requirements are slowly changing:
- Originally, I read "world", now I read "rest".
   The problem IMO is that "rest" is difficult to compute, because you
   need to sum on a set difference (all rows minus the top N rows)
   which you can only build by first determining the top N.
- You started asking for 2005, now show 2005 + 2004 (minor).

If you really want "top N, and the sum of all others", IMHO the best way 
is to do both the limitation and the summing of the other rows in your 
application:

Select Country, Sum(Sales) AS Sales
        From myTable
        Where Year=2005
        Group By Country
        Order By Sales DESC

Loop over the results, display the first N, do the summing for all 
others, display that sum.

HTH,
Joerg

-- 
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
Thread
Top N selections + rest rowC.R.Vegelin25 Apr
  • Re: Top N selections + rest rowBarry25 Apr
  • Re: Top N selections + rest rowMartijn Tonies25 Apr
  • Re: Top N selections + rest rowC.R.Vegelin25 Apr
    • Re: Top N selections + rest rowShawn Green25 Apr
      • Re: Top N selections + rest rowMartijn Tonies25 Apr
      • Re: Top N selections + rest rowJoerg Bruehe25 Apr
      • Re: Top N selections + rest rowC.R.Vegelin25 Apr
      • Re: Top N selections + rest rowC.R.Vegelin25 Apr
        • Re: Top N selections + rest rowJoerg Bruehe25 Apr