SQL: how to display top 5 then sum the rest

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

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
--

Thanks. Is "WITH TIES" a build in function, I can't get this working

sweetpotatop@yahoo.com  wrote:
> Thanks. Is "WITH TIES" a build in function, I can't get this working

It is in 7.0/2000/2005.

Always tell us what version you are using.

What does "can't get this working" mean? Error message? Wrong result?
Please post some code to reproduce the problem.

--
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
--

OLAP.

It ran ok if I am just running only the middle part

SELECT TOP 5 WITH TIES company_id
FROM sales
GROUP BY company_id
ORDER BY SUM(sale_amt) DESC

But if I ran the whole query, it gave me Invalid column name
'WITHTIES', for some reason, WithTies became one word when I ran it.

Actually, I figure out "WITH TIES" isn't the function that I am looking
at.

Let me explain my scenario again. I want to see the top 5 compies'
sales, but any other companies' sales total with be sum up to a
category called "Other"

e.g.
Company    Total Sales
Co. 1          $200
Co. 2          $150
Co. 3          $120
Co. 4          $100
Co. 5          $90
Other          $900

Actually, I tried the SQL and I think it does work as desired.  You
don't get a pretty label of "Other" for the sum of everything else, but
it works as advertised.  I am not familiar with the "WITH TIES" syntax
either, so I was curious and tried it in Northwind:

SELECT T.ProductID, SUM(Quantity)
FROM [Order Details] D1
LEFT JOIN (SELECT TOP 5 WITH TIES ProductID
FROM [Order Details]
GROUP BY ProductID
ORDER BY SUM(Quantity) DESC) AS T
ON D1.ProductID = T.ProductID
GROUP BY T.ProductID
ORDER BY SUM(Quantity) DESC
ORDER BY T.ProductID

The above returned a list of 6 records.  5 with product ID's and the
sum of their quantity, and another row with no value for ProductID and
a sum of the quantity for all of those.  If you do

select TOP 5 ProductID, SUM(Quantity)
from [Order Details]
group by ProductID
order by ProductID

you'll see the same 5 records that get returned in the first query.
Pretty slick actually.  Anyway, I thought it might help you test it if
you had an example from a table you can get to.

I ran this in Query Analyzer vers 8.00.194 and I'm connecting to a
server version Microsoft SQL Server  2000 - 8.00.760.  I didn't
experience the problem you had with the words "WITH TIES" getting
merged into one word.

Hope it helps,
Teresa Masino

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(Company
ID, 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_REPLYI
NG_dportas@acm.org> wrote in message
news:1144336214.680248.99050@v46g2000cwv.googlegroups.com...
> sweetpotatop@yahoo.com wrote: 
>
> 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
> --
>