List:General Discussion« Previous MessageNext Message »
From:C.R.Vegelin Date:April 25 2006 1:53pm
Subject:Re: Top N selections + rest row
View as plain text  
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%.

Regards, Cor


----- Original Message ----- 
From: "Joerg Bruehe" <joerg@stripped>
To: "Shawn Green" <sgreen_mysql@stripped>; "C.R.Vegelin" 
<cr.vegelin@stripped>
Cc: <mysql@stripped>
Sent: Tuesday, April 25, 2006 2:17 PM
Subject: Re: Top N selections + rest row


> Hi Shawn, Cor, all!
>
>
> Shawn Green wrote:
>>
>> --- "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
>>>
>>
>> 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
>
> I checked it here:
> http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
>
> It seems that "WITH ROLLUP" is not adequate for Cor's needs, see this 
> quote:
> | LIMIT can be used to restrict the number of rows returned to the
> | client. LIMIT is applied after ROLLUP, so the limit applies against
> | the extra rows added by ROLLUP.
>
>
> Cor,
> what about a UNION?
>
> Untested:
>
> ( Select Country, Sum(Sales) AS Sales
>        From myTable
>        Where Year=2005
>        Group By Country
>        Order By Sales DESC
>        LIMIT 25 )
> UNION
> ( SELECT "World", Sum(Sales) AS Sales
>        From myTable
>        Where Year=2005 ) ;
>
> Note the extra parentheses, according to the manual they are needed to 
> ensure that the limit is applied to the first select only.
>
>
> HTH,
> Joerg
>
> -- 
> Joerg Bruehe, Senior Production Engineer
> MySQL AB, www.mysql.com
> Office:  (+49 30) 417 01 487     VoIP: 4464@stripped
> 


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