Help: how to generate Quintiles

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help: how to generate Quintiles

Author

Topic

lovecherry
Starting Member

3 Posts

Posted - 2006-12-08 : 23:36:24

Suppose I have only two fields: ID and Sales. I want to know which IDs are generating the top 20% of the sales. Any good idea?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-12-09 : 01:34:41

This query will give you the top 20% of ID's which have generated the most sales

Table: TB1(ID,Sales)

select b.* from
(Select Dense_Rank() OVER (ORDER BY a1.Sales desc) AS 'Rank',a1.*
from (select a1.ID, Sum(A1.sales) as sales
from tb1 a1 GROUP BY a1.ID) a1 ) b
where b.Rank <=
(Select count(*) * .2 -- This is where I set it to 20%
from tb1)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-09 : 02:42:17

The suggested query may work in sql server 2005. You need to convert that code to work in sql server 2000(If you use it)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-10 : 16:04:31

Vinne, that will give you the top 20% salesmen.
OP wanted which salesmen generate the top 20% of the sales...

This is the test data

declare	@sales table (empid int, sales money)

insert	@sales
select	100, 200 union all
select	101, 550 union all
select	102, 200 union all
select	103, 125 union all
select	104, 500 union all
select	105, 300 union all
select	106, 400 union all
select	107, 215

select		t1.empid,
		t1.sales,
		(select sum(t2.sales) from @sales t2 where t2.sales >= t1.sales) acc
from		@sales t1
order by	t1.sales desc,
		t1.empid

How will 20% of 2490 (498 dollars) be compared to sales?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources