When you use ROLLUP, you cannot also use an ORDER BY clause to sort the
----- Original Message -----
From: "Shawn Green" <sgreen_mysql@stripped>
To: "C.R.Vegelin" <cr.vegelin@stripped>; "Martijn Tonies"
<m.tonies@stripped>; <mysql@stripped>; <Barry@stripped>
Sent: Tuesday, April 25, 2006 1:18 PM
Subject: Re: Top N selections + rest row
> --- "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
>> 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;
> Shawn Green
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around