Query for finding rows whose sum constitues percentage of total su

Hi.
Given a table that looks like:
unique_ID, some_amount
1 10
2 15
3 7
4 20
5 8
6 7
...
What i want is to find the rows that contribute most to the total sum of the
column and then cut off at a given percentage. IE if i'd like to find the top
30% contributers to the total sum in the example above the result would be
row 4 (total being 67 and row 4 contributes with 30% of that).
Any help very much appreciated.
/fr
DROP Table #SomeTable
CREATE TABLE #SomeTable
(
unique_id INT Identity(1,1),
some_Amount INT
)
INSERt INTO #SomeTable(Some_Amount)
SELECT 10
INSERt INTO #SomeTable(Some_Amount)
SELECT 15
INSERt INTO #SomeTable(Some_Amount)
SELECT 7
INSERt INTO #SomeTable(Some_Amount)
SELECT 20
INSERt INTO #SomeTable(Some_Amount)
SELECT 8
INSERt INTO #SomeTable(Some_Amount)
SELECT 7
DECLARE @.Percentage DECIMAL(20,2)
SET @.Percentage = 0.25
Select * from #SomeTable
where some_amount >=
(Select @.Percentage * SUM(some_amount) from #SomeTable)
|||hi ragnar,
here's a script
I put the amount on a table with id and amount fields
hope it helps
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr
|||hi,
you must cast the numerator in some datatype that accept the decimal place.
In my case i make it money. Other wise you get zero and then mulitply it with
100 to get the percentage. the rest is up to you
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr
|||hi,
here's an additional help
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1 x
where cast(amount as money)/(select sum(amount) from test1)*100 > 25
order by 3
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr
|||Try,
select 30 percent *
from t1
order by some_amount desc
-- or
select 30 percent with ties *
from t1
order by some_amount desc
AMB
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr
sql