CODEWITHSUNDEEP
sqlsql-server
jrob24
jrob24

Reputation: 454

Sum Quantity and Filter Results

I have the following table with order id's and quantities. I need to be able to sum the quantity and retrieve the id's that that equal less than the provided number. 

| id   | quantity |
|------|----------|
| 100  | 1        |
| 200  | 25       | 
| 300  | 15       |

For example, I need the id's where the sum of quantity equals less than 25.

When I try the following it only provides me the first id (100).

Select *
from (
   select *,
   SUM (Quantity) OVER (ORDER BY Id) AS SumQuantity
   from dbo.Orders
) as A
where SumQuantity <= 25

Is it possible to adjust this query where it will provide me id 100 and 300, since the sum total of those orders is less than 25?

I know I can use a where clause on for quantity less than 25, but the important thing here is I need to be able to sum the quantity and pull id's that give me less than the provided number.

Thank you in advance!

Upvotes: 1

Views: 538

Answers (4)

Michał Turczyn
Michał Turczyn

Reputation: 37500

cross join is perfect for this task, try:

declare @tbl table (id int, quantity int);
insert into @tbl values
(100, 1), (200, 25), (300, 15), (400, 10);

select distinct case when t1.id > t2.id then t1.id else t2.id end,
                case when t1.id < t2.id then t1.id else t2.id end
from @tbl t1
cross join @tbl t2
where t1.id <> t2.id
and t1.quantity + t2.quantity < 25

Upvotes: 1

pwilcox
pwilcox

Reputation: 5763

Setup:

Your threshold can vary, so let's make it into a variable:

declare @threshold int = 25;

But I also imagine that your table values can vary, like if we add another row only having a quantity of 2:

declare @orders table (id int, quantity int)
insert @orders values (100,1), (200,25), (300,15), (400, 2);

Solution:

For this, we'll need a recursive kind of cross joining:

with

    traverse as (

        select      ids = convert(nvarchar(255), id), 
                    id, 
                    quantity
        from        @orders
        where       quantity < @threshold

        union all
        select      ids = 
                        convert(nvarchar(255), tv.ids + ',' +
                        convert(nvarchar(255), o.id)),
                    o.id,
                    quantity = tv.quantity + o.quantity
        from        traverse tv
        cross join  @orders o 
        where       tv.id < o.id
        and         tv.quantity + o.quantity < @threshold

    )

    select   t.ids, t.quantity
    from     traverse t;

which will produce:

enter image description here

Explanation:

The above code is an algorithm that builds a tree. It starts with your base id's and quantities as nodes (the anchor part of the CTE). It trims anything not meeting the threshold.

It then adds edges by cross joining with orders table again (the recursive part of the CTE), but it only includes the following:

  • Id's that are greater than the last id considered in the current node (this is so that we avoid duplicate considerations, such as ids = '300,400' and ids = '400,300').
  • Ids where the sum of quantities is less than the threshold.

Warnings:

But beware, the type of problem you're considering will have computational complexity considerations. But because of the trimming conditions, it will be more efficient than doing all the cross joins first and then filtering the result set at the end.

Also, keep in mind that you may get rows in your table where there is no single set of numbers that will sum up to less than 25. Rather, you can get different paths to that sum. The way I produce the results here will help you identify such a situation.

Upvotes: 1

forpas
forpas

Reputation: 164204

Group by Id and set the condition in the HAVING clause:

select Id, SUM(Quantity) AS SumQuantity
from Orders
group by Id
having SUM(Quantity) <= 25

See the demo.
Results:

Id  | SumQuantity
100 |           1
200 |          25
300 |          15

If you want to include all the columns you can modify your query to not ORDER BY id but PARTITION BY id:

select *
from (
   select *,
   SUM (Quantity) OVER (PARTITION BY Id) AS SumQuantity
   from Orders
) as A
where SumQuantity <= 25

For this dataset:

CREATE TABLE Orders([id] varchar(6), [quantity] int);
INSERT INTO Orders([id], [quantity])VALUES
    ('100', '1'), ('100', '2'),
    ('200', '25'), ('200', '3'),
    ('300', '15'), ('300', '5');

Results:

id  | quantity | SumQuantity
100 |        1 |           3
100 |        2 |           3
300 |       15 |          20
300 |        5 |          20

See the demo.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Perhaps you want to order by the quantity instead of id?

Select o.*
from (select o.*, SUM (Quantity) OVER (ORDER BY quantity) AS SumQuantity
      from dbo.Orders
      ) o
where SumQuantity <= 25;

This chooses the smallest values so you will get the most rows.

Upvotes: 1

Related Questions