4

assume I have a table of this kind:

A B 3
C D 1
E F 2
G H 4

The sum of the last column is 10, and I want the biggest values that sum up to at least 60% of the total value. So, in this case, it will return

G H 4
A B 3

It goes up to 70% but if only the 1st value was selected, it will only go up to 40%. Even though there could be a combination that will return exactly 60%, we want to take the largest numbers.

So, I think I know how to sort the values from biggest to smallest and how to sum up all the values, but I don't know how to then take only lines that sum up to 60%.

n00b programmer
  • 2,671
  • 7
  • 41
  • 56
  • This is basically calculating running totals. What RDBMS is this for? – Martin Smith May 04 '11 at 12:52
  • Do you need a percentage of the total or are you just going for a target number? If you had 100 different values and you wanted the sum of all records up to but not over a certain number (453 for example) would that work? – Christopher Klein May 04 '11 at 12:54

2 Answers2

4
--save the whole sum into a variable
summa = select sum(val) from sometable;

select * 
  from sometable o 
 where (
        select sum(val) 
          from sometable i 
         where i.val <= o.val
       ) >= 0.6*summa;
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
bpgergo
  • 15,669
  • 5
  • 44
  • 68
3

I think this gives you the correct result. Need to work with a temporary table though, not sure if this can be avoided.

DECLARE @total bigint

select @total = SUM(value) from SampleTable

select st.*, 
convert(decimal(10,2), (select SUM(value) from SampleTable st2 where st2.Value >= st.Value))/@total as percentage
into #temptable
from sampletable st

select * from #temptable 
where Value >= (select max(Value) from #temptable where percentage >= 0.6)
jeroenh
  • 26,362
  • 10
  • 73
  • 104