Posted by David Portas on 04/06/06 18:10

sweetpotatop@yahoo.com wrote:
> Hello,
>
> I would like to query the top 5 best companies' sales (total sales),
> then total the rest, what is the quickest and effective SQL to query
> it?
>
>
> Thanks in advance

Please include DDL and sample data so that we don't have to guess at
your requirements. Here's my untested guess:

SELECT T.company_id, SUM(sale_amt) AS sale_amt
FROM sales AS S
LEFT JOIN
(SELECT TOP 5 WITH TIES company_id
FROM sales
GROUP BY company_id
ORDER BY SUM(sale_amt) DESC) AS T
ON S.company_id = T.company_id
GROUP BY T.company_id ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]