List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:April 25 2006 12:33pm
Subject:Re: Top N selections + rest row
View as plain text  
> > Thanks Martijn, Barry,
> > I was wondering whether it could be done in a single query.
> > I want users to decide how many countries they want,
> > and show world sales on top of report followed by the N countries.
> > This to enable relative country shares, both for reporting and
> > graphs.
> > For example, Top-10 countries + Rest in a pie graph.
> > So I need one additional row in the Top-N query.
> > >
> > >>Anybody with smart ideas to get Top-N rows plus the rest row ?
> > >>Suppose I have ranked sales values descending for 2005, like:
> > >>Select Country, Sum(Sales) AS Sales From myTable
> > >>Where Year=2005 Group By Country
> > >>Order By Sales DESC LIMIT 25;
> > >>
> > >>Then I get Top-25 sales rows, but I also want a rest row,
> > >>where all 26 rows represent the world total.
> > >>I'm using MySQL 5.0.15.
> > >
> > > What about just removing the LIMIT clause?
> > >
> > > Or, alternatively, do a skip of the first 25 rows? (check the docs
> > > for that)
> > >
> > >
> > > Do you want to get this in a single result, or additional result?
> 
> Have you considered using the WITH ROLLUP modifier?
> 
> Select Country
>   , Sum(Sales) AS Sales 
> >From myTable
> Where Year=2005 
> Group By Country WITH ROLLUP
> Order By Sales DESC 
> LIMIT 25;
> 
> http://dev.mysql.com/doc/refman/5.0/en/select.html

That sounds like what he needs, excellent :-)

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.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