|
Home > Archive > Microsoft SQL Server forum > April 2006 > SQL: how to display top 5 then sum the rest
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL: how to display top 5 then sum the rest
|
|
| sweetpotatop@yahoo.com 2006-04-06, 11:30 am |
| 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 2006-04-06, 11:30 am |
| 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
--
| |
| sweetpotatop@yahoo.com 2006-04-06, 11:30 am |
| Thanks. Is "WITH TIES" a build in function, I can't get this working
| |
| David Portas 2006-04-06, 11:30 am |
| 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
--
| |
|
|
| sweetpotatop@yahoo.com 2006-04-07, 7:35 am |
| 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@yahoo.com 2006-04-07, 9:30 am |
| 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 2006-04-07, 1:29 pm |
| 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 2006-04-07, 8:25 pm |
| 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
> --
>
|
|
|
|
|