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" <[EMAIL PROTECTED]>
To: "Shawn Green" <[EMAIL PROTECTED]>; "C.R.Vegelin"
<[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
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" <[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]
