MS SQL Server - Query for finding rows whose sum constitues percentage of total su

Author

Query for finding rows whose sum constitues percentage of total su

Ragnar

2005-09-12, 3:23 am

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

Jens

2005-09-12, 7:23 am

DROP Table #SomeTable

CREATE TABLE #SomeTable
(
unique_id INT Identity(1,1),
some_Amount INT
)

INSERt INTO #SomeTable(Some_Amou
nt)
SELECT 10

INSERt INTO #SomeTable(Some_Amou
nt)
SELECT 15

INSERt INTO #SomeTable(Some_Amou
nt)
SELECT 7

INSERt INTO #SomeTable(Some_Amou
nt)
SELECT 20

INSERt INTO #SomeTable(Some_Amou
nt)
SELECT 8

INSERt INTO #SomeTable(Some_Amou
nt)
SELECT 7

DECLARE @Percentage DECIMAL(20,2)
SET @Percentage = 0.25

Select * from #SomeTable
where some_amount >=
(Select @Percentage * SUM(some_amount) from #SomeTable)

Jose G. de Jesus Jr MCP, MCDBA

2005-09-12, 7:23 am

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

Jose G. de Jesus Jr MCP, MCDBA

2005-09-12, 7:23 am

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

Jose G. de Jesus Jr MCP, MCDBA

2005-09-12, 7:23 am

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

Alejandro Mesa

2005-09-12, 9:24 am

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

Sponsored Links