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
>
