SQL: how to display top 5 then sum the rest

[sweetpotatop]

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

[David Portas]

sw**********@ya hoo.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
--

[sweetpotatop]

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

[David Portas]

sw**********@ya hoo.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/m...S,SQL.90).aspx
--

[Doug]

OLAP.

[sweetpotatop]

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.

[sweetpotatop]

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

[Teresa Masino]

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

[DickChristoph]

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(Compa nyID int not null Primary Key,
CompanyName varchar(20))

Create Table CompanySales(Ro wID int not null identity(1,1) Primary Key,
CompanyID int references Companies(Compa nyID),
Date SmallDatetime,
Amount decimal(18,2))
insert Companies values(1,'Compa ny 1')
insert Companies values(2,'Compa ny 2')
insert Companies values(3,'Compa ny 3')
insert Companies values(4,'Compa ny 4')
insert Companies values(5,'Compa ny 5')
insert Companies values(6,'Compa ny 6')
insert Companies values(7,'Compa ny 7')
insert Companies values(8,'Compa ny 8')
insert Companies values(9,'Compa ny 9')
insert Companies values(10,'Comp any 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(Co mpanyID, Date, Amount) values (@Co, @Dt,@Amt)
set @Counter = @Counter + 1
end

SELECT T.companyid, coalesce(c.comp anyname,'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******** *************@v 46g2000cwv.goog legroups.com...

sw**********@ya hoo.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
--