Hi
Here is a slight modication to Davids SQL statement which with my sample
data results in the following, Note that CompanyNames are included and
'Other' appears at the bottom of the list.
Run on SQL 2000.
companyid CompanyName Amount
----------- -------------------- ---------------
3 Company 3 55599.83
5 Company 5 55468.11
1 Company 1 54803.95
10 Company 10 53781.68
8 Company 8 51504.47
NULL Other 235368.75
Create Table Companies(CompanyID int not null Primary Key,
CompanyName varchar(20))
Create Table CompanySales(RowID int not null identity(1,1) Primary Key,
CompanyID int references Companies(CompanyID),
Date SmallDatetime,
Amount decimal(18,2))
insert Companies values(1,'Company 1')
insert Companies values(2,'Company 2')
insert Companies values(3,'Company 3')
insert Companies values(4,'Company 4')
insert Companies values(5,'Company 5')
insert Companies values(6,'Company 6')
insert Companies values(7,'Company 7')
insert Companies values(8,'Company 8')
insert Companies values(9,'Company 9')
insert Companies values(10,'Company 10')
Declare @Counter int
Declare @Co int
Declare @dt SmallDateTime
Declare @amt Decimal(18,2)
set @Counter = 1
while @Counter <> 1000
begin
set @Co = 1 + (rand() * 10.0)
set @dt = cast('1/1/2006' as smalldatetime) + (rand() * 365)
set @amt = 1 + rand() * 1000
insert CompanySales(CompanyID, Date, Amount) values (@Co, @Dt,@Amt)
set @Counter = @Counter + 1
end
SELECT T.companyid, coalesce(c.companyname,'Other') as CompanyName,
SUM(Amount) AS Amount
FROM CompanySales AS S
LEFT JOIN
(SELECT TOP 5 WITH TIES companyid
FROM CompanySales
GROUP BY companyid
ORDER BY SUM(Amount) DESC) AS T
ON S.companyid = T.companyid
left join Companies c
on t.companyid = c.companyid
GROUP BY T.companyid, c.companyName
order by case when t.companyid is null then 1 else 0 end, sum(Amount) desc
--
-Dick Christoph
dc******@mn.rr.com
612-724-9282
"David Portas" <RE****************************@acm.org> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.com...
sw**********@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/m...S,SQL.90).aspx
--