CODEWITHSUNDEEP
sqlt-sqlpercentage
Robert Pulido
Robert Pulido

Reputation: 81

Select Rows who's Sum Value = 80% of the Total

Here is an example the business problem.

I have 10 sales that resulted in negative margin. We want to review these records, we generally use the 20/80 rule in reviews. That is 20 percent of the sales will likely represent 80 of the negative margin.

So with the below records....

+----+-------+
| ID | Value |
+----+-------+
|  1 |    30 |
|  2 |    30 |
|  3 |    20 |
|  4 |    10 |
|  5 |     5 |
|  6 |     5 |
|  7 |     2 |
|  8 |     2 |
|  9 |     1 |
| 10 |     1 |
+----+-------+

I would want to return...

+----+-------+
| ID | Value |
+----+-------+
|  1 |    30 |
|  2 |    30 |
|  3 |    20 |
|  4 |    10 |
+----+-------+

The Total of Value is 106, 80% is then 84.8. I need all the records, sorted descending who sum value gets me to at least 84.8

We use Microsoft APS PDW SQL, but can process on SMP if needed.

Upvotes: 1

Views: 3533

Answers (3)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Assuming window functions are supported, you can use

with cte as (select id,value
             ,sum(value) over(order by value desc,id) as running_sum 
             ,sum(value) over() as total
             from tbl
            ) 
select id,value from cte where running_sum < total*0.8
union all
select top 1 id,value from cte where running_sum >= total*0.8 order by value desc

Upvotes: 4

Richard Mneyan
Richard Mneyan

Reputation: 692

This link could be useful, it calculates running totals:

https://www.codeproject.com/Articles/300785/Calculating-simple-running-totals-in-SQL-Server

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95043

One way is to use running totals:

select
  id,
  value
from
(
  select
    id,
    value,
    sum(value) over () as total,
    sum(value) over (order by value desc) as till_here,
    sum(value) over (order by value desc rows between unbounded preceding and 1 preceding)
      as till_prev
  from mytable
) summed_up
where till_here * 1.0 / total <= 0.8
   or (till_here * 1.0 / total >= 0.8 and coalesce(till_prev, 0) * 1.0 / total < 0.8)
order by value desc;

Upvotes: 0

Related Questions