Re: Top N selections + rest row

[Joerg Bruehe]

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:  ([phone]     VoIP: 4464@stripped