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
>>For example, Top-10 countries + Rest in a pie graph.
>>So I need one additional row in the Top-N query.
> Have you considered using the WITH ROLLUP modifier?
> Select Country
> , Sum(Sales) AS Sales
> Where Year=2005
> Group By Country WITH ROLLUP
> Order By Sales DESC
> LIMIT 25;
I checked it here:
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.
what about a UNION?
( Select Country, Sum(Sales) AS Sales
Group By Country
Order By Sales DESC
LIMIT 25 )
( SELECT "World", Sum(Sales) AS Sales
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.
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
Office: (+49 30) 417 01 487 VoIP: 4464@stripped