|
|
Posted by DickChristoph on 10/02/38 11:44
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
dchristo@mn.rr.com
612-724-9282
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:1144336214.680248.99050@v46g2000cwv.googlegroups.com...
> 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
> --
>
Navigation:
[Reply to this message]
|