CODEWITHSUNDEEP
sqlsql-servert-sql
unruledboy
unruledboy

Reputation: 2333

get certain record while sum of previous records greater than a pecentage in SQL Server

Table: FooData

ID   Count
1    54
2    42
3    33
4    25
5    16
6    9
8    5
9    3
10   2

I want to fetch the record that sum of Count column is greater than a certain percentage like 90.

For this example, the sum of all the records is 189, and 90% of it is 170.1, we can see the sum from ID 1 to 6 is 179, and it is greater than 170.1 so the record ID 6 should be returned.

btw, temporary table is not allowed because I need to do it in a function.

Upvotes: 2

Views: 1337

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Another version of a triangular join.

declare @T table(ID int primary key, [Count] int)
insert into @T values (1, 54), (2, 42), (3, 33),(4, 25), (5, 16), (6, 9), (8, 5), (9, 3),(10, 2)

;with R(ID, [Count], Running) as
(
  select T1.ID, 
         T1.[Count],
         cast(T3.[Count] as float)
  from @T as T1
    cross apply (select sum(T2.[Count])
                 from @T as T2
                 where T1.ID >= T2.ID) as T3([Count])
),
T(Total) as
(
  select sum([Count])
  from @T
)
select top 1 R.ID, R.[Count], R.Running
from R
  inner join T
    on R.Running / T.Total > 0.9
order by R.ID

Upvotes: 3

8kb
8kb

Reputation: 11406

Try this:

SELECT TOP 1 
  t2.id,
  SUM(t1.value) AS runningTotal
FROM FooData t1 
INNER JOIN FooData t2 
  ON t1.id <= t2.id 
GROUP BY t2.id
HAVING SUM(t1.value) * 100. / 
  (SELECT SUM(value) FROM @FooData) > 90
ORDER BY SUM(t1.value)

But also be aware of the potential performance issue with triangular joins in running totals.

Upvotes: 1

Related Questions