List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:April 25 2006 12:18pm
Subject:Re: Top N selections + rest row
View as plain text  

--- "C.R.Vegelin" <cr.vegelin@stripped> wrote:

> 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.
> Regards, Cor
> 
> ----- Original Message ----- 
> From: "Martijn Tonies" <m.tonies@stripped>
> To: <mysql@stripped>
> Sent: Tuesday, April 25, 2006 11:06 AM
> Subject: Re: Top N selections + rest row
> 
> 
> > Hi,
> >
> >>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?
> >
> > Martijn Tonies
> > Database Workbench - development tool for MySQL, 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
> >

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

Shawn Green

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.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