Hi Shawn, Cor, all!
Shawn Green wrote:
--- "C.R.Vegelin" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
