Percentage of Totals - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Percentage of Totals

Author

Topic

Wonky27
Starting Member

1 Post

Posted - 2006-11-14 : 09:34:16

I am relatively new to SQL i.e. I know the basics!

What I am trying to do is: I have a database of customer spend and I am trying to segment them into certain %ages of the search results. So, for example, find all of the customers that are in the top 10% of spenders (I also want to only select customers with a spend of greater than x!). I am trying to do this using a Case When but feel that I may be out of my depth.

Please help if you can!!

Thanks, Kris

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 09:48:40

select top 10 percent <col-list>
from yourtable
where <some criteria>
order by <somecol or some aggregation>

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-14 : 09:48:55

Try

Select customer from
(
Select customer, sum(spend)*1.0/(Select sum(Spend) from table) as spend_percent from table
group by customer
) T where spend_percent>x

Madhivanan

Failing to plan is Planning to fail

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-11-14 : 12:18:02

I think I read it the same as Peter. Select the top 10% of the ones that spent more than a given X, not select the customers that spent more than x%.

clarification?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 12:22:46

select top 10 percent <customername>
from yourtable
where spend > $50
group by <customername>
order by SUM(spend) DESC

This query selects top 10 percent of the customers and their summed spendings, disregarding single spendings under 10 dollars.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources